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

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.

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
  • 7 replies
  • 2431 views
  • 4 likes
  • 7 in conversation