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

Reply
Regular Contributor
Regular Contributor
Posts: 221

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

[ Edited ]

I am trying to modify a macro to export each file in a target library to a separate Excel file.

 

I have this but don't understand it enough to successfully modify it:

 

%macro SASToExcel(ExportLibrary=);

    ods output members = _Members;
    proc datasets lib = &ExportLibrary; run; quit;

    proc sql;
        select count(Name) into :NumOfDatasets from _Members;
		select Name into :Name from _Members;
        select Name into :Dataset1-:Dataset%trim(%left(&NumOfDatasets)) from _Members;
    quit;

    %do index = 1 %to &NumOfDatasets;
        %let myoutfile = %str(&Name);
		proc export data=&ExportLibrary..&&Dataset&index.
        outfile="K:\MS_PROGS\cip_codes\&myoutfile..xlsx"
        dbms=excel replace;
        run;
    %end;

    proc datasets;
        delete _Members;
    quit;

%mend;

%SASToExcel(ExportLibrary=work);

This runs, but it creates one Excel file named after the first dataset in the library with each SAS dataset in the library as a sheet in that file.  I want each dataset as a file named by its SAS dataset name.

 

I think I am close, but don't know how to proceed.  Help, oh Great Macro Gurus??

Super User
Posts: 21,572

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

        outfile="K:\MS_PROGS\cip_codes\&myoutfile..xlsx"

 

You need to make sure that is a unique file name for each, but myoutfile isn't defined anywhere in your code. 

So you need to modify that line, with the name you want for the file. That should be all the changes needed that I see. 

Super User
Posts: 21,572

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

PS. If you want to understand it, add comments to the code. Future you will thank you.
Regular Contributor
Regular Contributor
Posts: 221

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

I was trying to define &myoutfile.  That's where  I fall down, i think.

 

 

        %let myoutfile = %str(&Name);

Super User
Posts: 21,572

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

 I want each dataset as a file named by its SAS dataset name.

 

 

You already have a macro variable that has the value you need to meet the requirement above, you don't need to create a new one. 

I have no idea where &name is coming from either so that's just as vague as outfile Smiley Happy

Regular Contributor
Regular Contributor
Posts: 221

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

 

You already have a macro variable that has the value you need to meet the requirement above,

 

 

I do? Which one?

 

Super User
Posts: 21,572

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

%macro SASToExcel(ExportLibrary=);

%* obtain list of datasets in the library;
    ods output members = _Members;
    proc datasets lib = &ExportLibrary; run; quit;

    proc sql;
*get the name of datasets into the list using Dataset1, Dataset2 etc;
        select Name into :Dataset1-
from _Members;
    quit;
*store the number of records to control the loop;
%let NumOfDatasets=&sqlobs;

%*loop to export all your data sets;
    %do index = 1 %to &NumOfDatasets;

		proc export data=&ExportLibrary..&&Dataset&index.
        outfile="K:\MS_PROGS\cip_codes\&&dataset&index..xlsx"
        dbms=excel replace;
        run;
    %end;

%*delete dataset of list of data sets in the library;
    proc datasets;
        delete _Members;
    quit;

%mend;

%SASToExcel(ExportLibrary=work);

Try the following, simplifed

Regular Contributor
Regular Contributor
Posts: 221

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

79: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN
      where the error has occurred.
ERROR 79-322: Expecting a :.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN
      where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.

Super User
Posts: 12,148

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

You can replace:

    ods output members = _Members;
    proc datasets lib = &ExportLibrary; run; quit;

    proc sql;
        select count(Name) into :NumOfDatasets from _Members;
		select Name into :Name from _Members;
        select Name into :Dataset1-:Dataset%trim(%left(&NumOfDatasets)) from _Members;
    quit;

with

 

proc sql noprint;
    select memName into :Dataset1-:Dataset9999
    from dictionary.tables
    where libname=upcase("&Exportlibrary");
quit;
%let NumOfDatasets=&sqlobs;
%put data sets: &NumOfDatasets;

The dictionary tables store the information that proc datasets reads so skip that step. The select into will create only has many of the DATASET macro variables as needed.

 

Any proc sql code sets the automatic macrovariable sqlobs from the last request, which in this case would be the number of member names in the library. The upcase is used incase you pass mixed or lower case to the macro for the export library as the value stored is upper case.

 

There is also no need with approach to delete a table as you didn't create one

Regular Contributor
Regular Contributor
Posts: 221

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

Had to change &&dataset&index..xlsx to  &&dataset&index. otherwise it created namexlsx.XLS.

 

Other than that I think it works!!

 

Thank you!

 

Ask a Question
Discussion stats
  • 9 replies
  • 99 views
  • 2 likes
  • 3 in conversation