Skip to content
9to5sas
  • Index
  • Glossary
Facebook Twitter Instagram Email RSS

  • Start Here
  • Base SAS
  • Advanced SASExpand
    • SAS Macros
    • PROC SQL
  • SAS/STATSExpand
    • SAS Analytics
    • Statistics
  • SAS Programs
9to5sas

9to5sas » SAS PROGRAMS » Generating Multiple Excel Files

Generating Multiple Excel Files

BySubhro Posted onJuly 21, 2020October 11, 2022 Last Updated:October 11, 2022
0 Comments

The macro is dynamic to generate multiple Excel files containing the frequency results for each unique BY-group. (e.g., Type). PROC SQL SELECT…

The macro is dynamic to generate multiple Excel files containing the frequency results for each unique BY-group. (e.g., Type).

PROC SQL SELECT code is embedded inside a macro in this SAS example.

The SELECT query processes the column (passed in the macro parameter) and then create a macro variable with the number of unique (distinct) value in the column, and a macro variable with the list of unique values separated with “-“

The macro uses the <a href="https://www.9to5sas.com/proc-freq-in-sas/">PROC FREQ</a> procedure, an iterative macro %DO statement, a %SCAN function, and WHERE= data set option to dynamically send the results to an Excel spreadsheet for each BY-group.

options symbolgen symbolgen mprint;
%macro MULTEXCELFILES(DS,COL,OUTPATH);
        * Get the count of unique values in the column;
        proc sql noprint;
                        /* number of unique values */
            select count(distinct &col) into :mprodtype_cnt 
            from &ds order by &col;
            select distinct &col into :mprodtype_lst separated by 
                "-" /* list of product types */
                from &ds order by &col;
        quit;

        %do j=1 %to &mprodtype_cnt;
            ods Excel file="&outpath/%SCAN(&mprodtype_lst,&j,-).xlsx" 
                style=styles.barrettsblue options(embedded_titles="yes");
            title "&col. - %SCAN(&mprodtype_lst,&j,-)";
            * Frequency of the column;

            proc freq data=&ds(where=(&col="%SCAN(&mprodtype_lst,&j,-)"));
                tables &col.;
            run;
ods Excel close;
%end;
%put &mprodtype_lst;
%mend MULTEXCELFILES;
/******* END OF FILE *******/

%multExcelfiles(ds=SASHELP.CARS, col=TYPE,
outpath=/home/subhroster20070/examples);
Generating Multiple Excel Files

Download this source code.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Check your inbox or spam folder to confirm your subscription.

Post Tags: #SAS macro Examples
Subhro

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.

Facebook Twitter Linkedin

Post navigation

Previous Previous
Ten Quick Uses of Proc Datasets
NextContinue
Macro To check if a variable exists in SAS dataset

SAS Tips in your inbox

Subscribe to 9to5sas for timely SAS tips and news delivered each month.
Learn about the latest articles, and code samples to keep your SAS skills fresh!

Your subscription is successful!

Recent Posts

  • Concatenate strings in SAS: The CAT Functions Demystified
  • 5 Techniques for Quickly Removing Leading Zeros in SAS
  • Troubleshoot Your Proc SQL Code Like a Pro with These 7 Automatic Macro Variables
  • 7 PROC SQL Options You Should Use to Debug Queries
  • How To Use The SAS Proc SQL Order By Statement?
9to5sas
  • Privacy Policy
  • Disclaimer
  • About
  • Contact
Facebook Twitter Instagram RSS Email Pinterest

Copyright © 2023 9TO5SAS, All rights reserved.

Scroll to top
  • 9to5sas Blueprint
  • About
  • About
  • Acceptable use policy
  • calculator
  • Confirm email
  • Contact
  • Contact
  • Cookie Policy
  • DISCLAIMER
  • Getting Started with SAS
  • Glossary
  • Index
  • Post #13801
  • Privacy Policy
  • Privacy policy
  • SAS Programs
  • Styles
  • Subscription confirmed
  • Terms and conditions
  • Thank You