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

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

HB — Wed, 24 Jan 2018 18:30:42 GMT

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??

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

Reeza — Wed, 24 Jan 2018 18:33:59 GMT

        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.

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

Reeza — Wed, 24 Jan 2018 18:34:56 GMT

PS. If you want to understand it, add comments to the code. Future you will thank you.

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

HB — Wed, 24 Jan 2018 18:36:13 GMT

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

%let myoutfile = %str(&Name);

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

Reeza — Wed, 24 Jan 2018 18:40:03 GMT

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 🙂

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

HB — Wed, 24 Jan 2018 18:43:02 GMT

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

I do? Which one?

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

Reeza — Wed, 24 Jan 2018 18:49:31 GMT

%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

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

HB — Wed, 24 Jan 2018 18:58:23 GMT

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.

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

ballardw — Wed, 24 Jan 2018 19:03:49 GMT

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

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

HB — Wed, 24 Jan 2018 19:13:44 GMT

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

Other than that I think it works!!

Thank you!