BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

7 REPLIES 7
BrunoMueller
SAS Super FREQ

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
Diamond | Level 26 RW9
Diamond | Level 26

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

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
Onyx | Level 15

@Kurt_Bremser,

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

Tom
Super User Tom
Super User

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
Super User
 Why not PROC COPY ? 
libname x xlsx 'c:\temp\x.xlsx';
proc copy in=work out=x;
select x1 x2;
run;   

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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