Help using Base SAS procedures

How to simplify the proc export?

Accepted Solution Solved
Reply
Super Contributor
Posts: 319
Accepted Solution

How to simplify the proc export?

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;


Accepted Solutions
Solution
‎07-01-2017 01:28 PM
Super User
Posts: 7,760

Re: How to simplify the proc export?

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
SAS Super FREQ
Posts: 708

Re: How to simplify the proc export?

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)

 

Super User
Super User
Posts: 7,942

Re: How to simplify the proc export?

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.  

 

 

 

 

 

 

 

Solution
‎07-01-2017 01:28 PM
Super User
Posts: 7,760

Re: How to simplify the proc export?

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,156

Re: How to simplify the proc export?

Posted in reply to KurtBremser

@KurtBremser,

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

Super User
Posts: 7,760

Re: How to simplify the proc export?


Haikuo wrote:

@KurtBremser,

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


<blush>

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,039

Re: How to simplify the proc export?

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;
Super User
Posts: 10,018

Re: How to simplify the proc export?

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 176 views
  • 4 likes
  • 7 in conversation