How to simplify the proc export?

[ybz12003]

Hello:

I have the 21 program are very similar as the following.  Is there a way to make the codes shorter?  Thanks.

%Let Folder=path;

%Let X1=ID;

%Let X2=Time;

%Let X3=Place;

PROC EXPORT DATA=&X1

outfile=&folder

dbms=xlsx replace;

sheet=&X1;

run;

　

PROC EXPORT DATA=&X2

outfile=&folder

dbms=xlsx replace;

sheet=&X2;

run;

PROC EXPORT DATA=&X3

outfile=&folder

dbms=xlsx replace;

sheet=&X3;

run;

[BrunoMueller]

This blog entry http://blogs.sas.com/content/sasdummy/2012/03/20/sas-program-by-processing/ by @ChrisHemedinger shows a very good example of a programming pattern executing the same code but with different values for certain statements and options.

In your case, you  would list all the different values for macro vars x1 - x3 in a SAS data set in one variable.

You can also use the following macro sample to work through a list:

%macro iterator(valueList=);
  %local i value;
  %let i = 1;
  %let value = %scan(&valueList, &i);
  %do %while( &value ne );
    %put NOTE: &sysmacroname processing &=value;
    /* do something here, your SAS code */

    %let i = %eval(&i + 1);
    %let value = %scan(&valueList, &i);
  %end;
%mend;

%iterator(valueList=ID Time Place)

[RW9]

Do you have SAS 9.4?  If so the you can use either:

libname excel tmp "file.xlsx";
data tmp.id;
  set id;
run;
...
libname tmp clear;

Or you can use ods excel.  Both create native Excel files, and ods excel will allow you to use proc report which gives you all the formatting options.  

[Kurt_Bremser · Accepted solution]

How to create repeating code:

Step 1: create one working instance of code:

proc export
  data=id
  outfile=path
  dbms=xlsx
  replace
;
sheet=id;
run;

Step 2: identify variable parts and replace with macro variables:

%let dataname=id;
%let folder=path;

proc export
  data=&dataname
  outfile=&folder
  dbms=xlsx
  replace
;
sheet=&dataname;
run;

 Step 3: wrap into a macro, use the macro variables as parameters and call that:

%macro repeat_code(dataname,folder);
proc export
  data=&dataname
  outfile=&folder
  dbms=xlsx
  replace
;
sheet=&dataname;
run;
%mend;

%repeat_code(id,path)

Step 4: create a means to repeat that code:

data control;
input dataname $ folder $;
cards;
id path
time path
place path
;
run;

data _null_;
set control;
call execute('%repeat_code(' !! trim(dataname) !! ',' !! trim(folder) !! ') ');
run;

Test at every step to make sure it's working before adding further complexity (Maxim 34). Scan the log thoroughly (Maxim 2).

[Haikuo]

@Kurt_Bremser,

Very nice write-up! Can be used as a How-to tutorial for Macro beginners.

[Kurt_Bremser]

---

@Haikuo wrote:

@Kurt_Bremser,

Very nice write-up! Can be used as a How-to tutorial for Macro beginners.

---

<blush>

[Tom]

If it is really just putting mutlple sheets into the same workbook then try using a LIBNAME instead of PROC EXPORT.

libname out xlsx "path" ;
proc copy inlib=work outlib=out;
 select ID Time Place;
run;

[Ksharp]

 Why not PROC COPY ? 
libname x xlsx 'c:\temp\x.xlsx';
proc copy in=work out=x;
select x1 x2;
run;