## DATA Step, Macro, Functions and more

Solved
Frequent Contributor
Posts: 88

Can someone please help me understand this macro step by step? I'm very new to sas so i'm having a hard time understanding macros.

This is the macro:

%macro handimp;

%let var1 = Intake;
%let var2 = Discharge;

%do k=1 %to 2;

proc import out= hand_&&var&k.._paper datafile= "&fpath\Hand_Wrist.xlsx"
dbms=xlsx replace;
getnames=yes;
sheet = "&&var&k";
run;

proc sort data = hand_&&var&k.._paper;
by client_id;
run;
%end;
%mend;

%handimp;

Questions

1. Why is this macro creating two macro variables called var1 and var2? Is this necessary? Is this meant to highlight the two sheets in my excel that I want to import?

2. What does the following line mean exactly:

%do k=1 %to 2;

thank you! If you answer can you please answer simply as I don't always understand all the terminilogy just yet!

Accepted Solutions
Solution
‎06-24-2016 09:34 AM
Super User
Posts: 23,773

It's a do loop...the start from - up to - end value.

Loop from 1 to 2 in this case.

It could easily be 1 to 20 if you wanted 20 loops.

And no, not observation are not being looped. This loops the entire procedure or portion of text.

Write the out the entire piece of code and replace each instance of k with a 1.

Then write out entire piece of code and replace each instance of k with a 2.

That will show you what the code is doing.

If running the code you can use the following to have the log display the details from each iteration.

Option MPRINT SYMBOLGEN;

All Replies
Super User
Posts: 6,785

There's too much to explain.  You will need to study SAS language first, then macro language, to come up to speed on these questions.

This might help.  First, appreciate that there is an earlier piece missing here.  Somehow &FPATH got defined and assigned a value.  Whatever its value is, the value gets substituted into the program where &FPATH appears.  When this macro runs, it generates the following SAS code (again, with &FPATH replaced):

proc import out= hand_Intake_paper datafile= "&fpath\Hand_Wrist.xlsx"

dbms=xlsx replace;
getnames=yes;
sheet = "Intake";
run;

proc sort data = hand_Intake_paper;
by client_id;
run;

proc import out= hand_Discharge_paper datafile= "&fpath\Hand_Wrist.xlsx"
dbms=xlsx replace;
getnames=yes;
sheet = "Discharge";
run;

proc sort data = hand_Discharge_paper;
by client_id;
run;

So &var1 and &var2 are being used ... think of them as replacing &&var&k when the macro runs.

Super User
Posts: 23,773

What are you trying to use this macro for? Is it something in code your working with or code your trying to adapt to something else?

Frequent Contributor
Posts: 88

I'm actually working with someone else's sas code and need to double check things for them. But also I'm just trying to learn it.

for right now I think I just need someone to explain what the bolded part in the code means. I've googled it but it doens't make a lot of sense to me in plain english.

Can someone explain that bolded section for me in general terms? Just so I have a high level understanding of what it is actually doing.

Super User
Posts: 23,773

It's a do loop start.

It will loop from start of do loop to the %end statement each time incrementing the value of k.

Frequent Contributor
Posts: 88

s does the do loop say start at k, and k being observation 1...what does the 2 mean? Does that just mean it's going to the next observation? so 1, 2, 3 etc?

Solution
‎06-24-2016 09:34 AM
Super User
Posts: 23,773

It's a do loop...the start from - up to - end value.

Loop from 1 to 2 in this case.

It could easily be 1 to 20 if you wanted 20 loops.

And no, not observation are not being looped. This loops the entire procedure or portion of text.

Write the out the entire piece of code and replace each instance of k with a 1.

Then write out entire piece of code and replace each instance of k with a 2.

That will show you what the code is doing.

If running the code you can use the following to have the log display the details from each iteration.

Option MPRINT SYMBOLGEN;

Frequent Contributor
Posts: 88

Thank you very much!

Super Contributor
Posts: 340

``````%macro handimp; /* Declares a macro function named handimp */

%let var1 = Intake;     /* declares a macrovariable var1 that takes the value "Intake" (wo quotes) */
%let var2 = Discharge;  /* declares a macrovariable var2 that takes the value "Discharge" (wo quotes) */

%do k=1 %to 2; /* Loops on k from 1 to 2 */

/* Imports data from an excel file, the name of which depends on k */
proc import out= hand_&&var&k.._paper  /* &&var&k. first, Intake then Discharge */
/* var&k => var1 or var2 | &&var&k => &var1 or &var2 */
datafile= "&fpath\Hand_Wrist.xlsx" /* macrovariable fpath should be initialized somewhere else */
dbms=xlsx replace;
getnames=yes;
sheet = "&&var&k";
run;

/* Sorts the newly created SAS dataset by client_id */
proc sort data = hand_&&var&k.._paper;
by client_id;
run;
%end; /* End of loop */

%mend; /* End of macro definition */

%handimp; /* Calls the macro handimp */``````
🔒 This topic is solved and locked.