topic Re: Macro to export each table in a library to an Excel file in SAS Programming

Macro to export each table in a library to an Excel file

brulard — Mon, 11 Mar 2019 15:53:29 GMT

Hello,

I'm looking for sample code or approach whereby I can export tables to excel (using PC SAS), where the following is true:

Using example below from two tables, where value of column Name should (a) be selected

when outputted to its own excel file and include the value in the Excel file name; and

(b) where the sheet name would be titled with the table name or a variation of.

In other words, if there are (a) 7 unique values in column Name, there should be 7 excel files generated; if there are two tables in library, each excel file should contain two sheets (more if more tables).

(This is a slight variation of prior posts from Reeza and ballardw here: https://communities.sas.com/t5/General-SAS-Programming/Macro-to-export-each-file-in-a-library-to-an-Excel-file/td-p/430575).

Sample raw data

data likes;
    length name $10 likes $10;
    input name $ likes $;
    datalines;
        Peter Cakes
        Peter Chocolate
        Simon Basketball
        Philip Apples
        Sam Oranges
        Paul Bananas
        John Cricket
        Frank Cats
        Frank Evita
        ;run;

data dislikes;
    length name $10 dislikes $10;
    input name $ dislikes $;
    datalines;
        Peter Carrots
        Peter Eggs
        Simon Pool
        Philip Bananas
        Sam Pears
        Paul Oreos
        John Bingo
        Frank Rats
        Frank Celery
        ;run;

Sample macro that works with the first table

%macro example2a; 

     proc sql noprint;
        select count(distinct name)
        into :obs
        from likes;
    quit;

    proc sql noprint;
        select distinct name
        into :name1-
        from likes;
    quit;

     %do i = 1 %to &obs;
        data im_.&&name&i;
            set likes;
            where name = "&&name&i";
        run;
    %end;

%mend;

%example2a

/*source: https://www.quanticate.com/blog/bid/58366/the-into-statement-in-proc-sql-to-create-macro-variables*/

Sample output of 1 file


%let filetitle=Monthly_Report;
%let drop=C:\Users\sxxxxx\temp\sas\;

PROC EXPORT 
      DATA=/*name*/
      OUTFILE= "&drop.&filetitle./*name*/.xlsx"
      DBMS=xlsx replace; 
      SHEET="/*Likes*/";
run;

PROC EXPORT 
      DATA=/*name*/
      OUTFILE= "&drop.&filetitle./*name*/.xlsx"
      DBMS=xlsx replace; 
      SHEET="/*Dislikes*/";
run;

thank you- I don't have plenty of experience creating dynamic outputs, so adding an explanation to your suggestion would be highly appreciated (note that i have downloaded and will begin to study the 1300+ page SAS Functions and call routine manual to educate myself on this topic).

Re: Macro to export each table in a library to an Excel file

tomrvincent — Mon, 11 Mar 2019 18:30:01 GMT

This is what I use:

%MACRO Export_Library(Libname,path,format=CSV);

DATA MEMBERS;
SET SASHELP.VMEMBER(WHERE=(LIBNAME = upcase("&Libname")));
RETAIN OBS 0;
OBS = OBS+1;
KEEP MEMNAME OBS;
RUN;

PROC SQL;
SELECT MIN(OBS), MAX(OBS) INTO :MIN, :MAX trimmed FROM MEMBERS;
QUIT;

%Local D;

%DO D = &MIN %TO &MAX;

PROC SQL;
SELECT COMPRESS(MEMNAME) INTO: Table trimmed
FROM MEMBERS
WHERE OBS=&D;
QUIT;

%let Table=%trim(&Table);

PROC EXPORT DBMS=&format DATA=&Libname..&Table
OUTFILE="&path./&Table..&format" replace;
RUN;

%END;

%MEND Export_Library;

Re: Macro to export each table in a library to an Excel file

brulard — Mon, 11 Mar 2019 18:32:04 GMT

Thanks Tom... I will try it

Re: Macro to export each table in a library to an Excel file

tomrvincent — Mon, 11 Mar 2019 18:33:49 GMT

It defaults out to CSV but you can change it to xls, xlsx, etc.

Re: Macro to export each table in a library to an Excel file

brulard — Wed, 20 Mar 2019 12:01:28 GMT

hello. So here is a tweak of Tom's and some other codes to export excel files (i) based on char values from a user 'name' column (eg., where values like John, Jane, etc.) , and (ii) table name(s) of all tables within given library (eg., work.table_a; ...table_b) such as the excel file name is the user 'name' value, and each sheet name is equal to the table names (where user name value is found)...there may be a more efficient means of coding this.

%Let Libname=IM_RE; /* edit libname here */
%put &libname;

%MACRO Export;

proc sql noprint;
   select count(distinct Business_Owner) /* Edit source column as needed */
   into :obs
   from &libname..quad1; /* Edit source table as needed*/
quit;

proc sql noprint;
   select distinct Business_Owner
   into :name1- notrim
   from &libname..quad1 (where=(Business_Owner ne ''));
quit;

/*edit: adding a 'distinct' in lieu of'group' segment*/
proc sql; create table test as
select distinct LIBNAME, memname
from dictionary.columns
WHERE LIBNAME=upcase("&Libname")
;quit;

DATA MEMBERS;
SET test;
RETAIN OBS 0;
OBS = OBS+1;
KEEP MEMNAME OBS;
RUN;

PROC SQL noprint;
SELECT MIN(OBS), MAX(OBS) INTO :MIN, :MAX trimmed FROM MEMBERS;
QUIT;

%Local D;

%DO D = &MIN %TO &MAX;
/*b*/
%do i = 1 %to &obs;
/*b*/
PROC SQL noprint;
SELECT COMPRESS(MEMNAME) INTO: Table trimmed
FROM MEMBERS
WHERE OBS=&D;
QUIT;

%let Table=%trim(&Table);


PROC EXPORT /*exports to a default unix server. Add a filepath to outfile as needed*/
      DATA=&libname..&Table (where=(Business_Owner= "&&name&i")) 
      OUTFILE="%qsubstr(&&name&i.,1).xlsx"
     DBMS=xlsx replace;
      SHEET="&Table";
     run;

     /* 19.03.18.9:00am –optional code to generate tables which is good 
     for data validation 
     data &libname..&Table._&&name&i;
         set &libname..&Table;
         where Business_Owner = "&&name&i";
     run;  */
    
%end;
%end;
%MEND Export;

Options symbolgen mlogic  ;
/*Remove above line when macro working .For efficiency, consider putting
Options NoSymbolgen nomlogic  ;
*/

%Export;