topic Re: How to simplify the proc export? in SAS Procedures

How to simplify the proc export?

ybz12003 — Tue, 20 Jun 2017 11:23:13 GMT

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;

Re: How to simplify the proc export?

BrunoMueller — Tue, 20 Jun 2017 11:44:12 GMT

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)

Re: How to simplify the proc export?

RW9 — Tue, 20 Jun 2017 12:08:29 GMT

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.

Re: How to simplify the proc export?

Kurt_Bremser — Tue, 20 Jun 2017 12:09:00 GMT

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).

Re: How to simplify the proc export?

Tom — Tue, 20 Jun 2017 12:21:45 GMT

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;

Re: How to simplify the proc export?

Ksharp — Tue, 20 Jun 2017 12:28:48 GMT

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

Re: How to simplify the proc export?

Haikuo — Tue, 20 Jun 2017 13:04:33 GMT

@Kurt_Bremser,

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

Re: How to simplify the proc export?

Kurt_Bremser — Tue, 20 Jun 2017 13:06:23 GMT

@Haikuo wrote:

@Kurt_Bremser,

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

<blush>