BookmarkSubscribeRSS Feed
HB
Barite | Level 11 HB
Barite | Level 11

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

9 REPLIES 9
Reeza
Super User
        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. 

Reeza
Super User
PS. If you want to understand it, add comments to the code. Future you will thank you.
HB
Barite | Level 11 HB
Barite | Level 11

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

 

 

        %let myoutfile = %str(&Name);

Reeza
Super User

 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 🙂

HB
Barite | Level 11 HB
Barite | Level 11

 

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

 

 

I do? Which one?

 

Reeza
Super User
%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

HB
Barite | Level 11 HB
Barite | Level 11

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.

ballardw
Super User

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

HB
Barite | Level 11 HB
Barite | Level 11

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

 

Other than that I think it works!!

 

Thank you!

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3742 views
  • 2 likes
  • 3 in conversation