topic Re: Iteration loops to macros in SAS Programming

Iteration loops to macros

wongmay — Fri, 18 Jul 2014 03:51:40 GMT

I am now writing a SQL to create an empty dataset with structured variable names for my school record as below. Is it possible for me to use macros with iteration loops for the PROC SQL ?

e.g. variable names : student_id, boy_000-boy_300, girl_000-girl_300 and school_total

proc sql;
create table student
    (student_id    char(3),
     boy_000     num format F3.,
     boy_001     num format F3.,
      ..........................................

     boy_299     num format F3.,
     boy_300     num format F3.,
     girl_000      num format F3.,
     girl_001      num format F3.,
      ..........................................

     girl_299    num format F3.,
     girl_300    num format F3.,
     school_total num format F5.);
quit;
run;

Many thanks !

regards

may

Re: Iteration loops to macros

Tom — Fri, 18 Jul 2014 04:15:22 GMT

Why use SQL to create a table? Much easier to use a data step where you will have full access to the SAS syntax.

data student ;

length student_id $3 boy_000-boy_300 girl_000-girl_300 school_total 8;

format boy: girl: 3. school_total 5.;

stop;

run;

Re: Iteration loops to macros

wongmay — Fri, 18 Jul 2014 07:43:20 GMT

Great help ! Thanks.

regards

may

Re: Iteration loops to macros

RW9 — Fri, 18 Jul 2014 08:50:43 GMT

Hi,

You could also generate the required syntax from a loop, see below. It depends on scenario really. IMO Tom's suggestion of the datastep is not really a huge saving. You still need to specify each variable, ok you can save typing num format=f3., but then that is what cut and paste is for. So its mainly style preference. I tend to favour SQL with each variable on a row so you can modify each individually quite straightforwardly. Where I find it harder to read a long string across the page and add extra, but that may just be me. One other consideration is if you are running code on multiple systems, e.g. if your using Toad and testing pass through code, switching to datastep wouldn't work then.

data _null_;

call execute('proc sql; create table STUDENT (student_id char(3)');

do i="boy_000","boy_001","boy_299";

call execute(','||i||' num format f3.');

end;

call execute(');quit;');

run;

Re: Iteration loops to macros

wongmay — Mon, 21 Jul 2014 01:04:00 GMT

Thank you RW9. I have modified the syntax below and it worked successfully. Both DATA STEP and PROC SQL are quite useful and help me so much.

*******************************************************************************************************

%symdel x;
%let tot_std=100;

data _NULL_;
call execute('proc sql; create table output.tt (student_id char(3)');

do i=1 to &tot_std;
call symput("x",put(i,z3.));

call execute(",boy_&x num format f3.");
end;

do i=1 to &tot_std;
call symput("x",put(i,z3.));

call execute(",girl_&x num format f3.");
end;

call execute(",student_total num format f3.");
call execute(");quit;");
run;

*******************************************************************************************************

Regards

may

Re: Iteration loops to macros

RW9 — Mon, 21 Jul 2014 08:26:55 GMT

Hi,

I would be very careful about using symput with call execute. There is a feature of call execute which will resolve macros at the time call execute is called, not afterwards. It may not affect here, but you will have times where code runs fine, but you only get one resolution to macros. I would re-write your code to avoid the use of macro variables, so instead of the call symputs and &'s, just (strip as using best) put the number to text. If you need tot_std as a macro variable, then you still can and set the variable in the datastep to that.

data _NULL_;

tot_std=100;
call execute('proc sql; create table output.tt (student_id char(3)');

do i=1 to tot_std;

call execute(",boy_"||strip(put(I,best.))||" num format f3.");
end;

do i=1 to &tot_std;

call execute(",girl_"||strip(put(I,best.))||" num format f3.");
end;

call execute(",student_total num format f3.); quit;");
run;

Re: Iteration loops to macros

wongmay — Mon, 21 Jul 2014 23:35:22 GMT

Work fine ! Thank you.

regards

may

Re: Iteration loops to macros

JonesSmith — Thu, 24 Jul 2014 05:35:53 GMT

Hi, RW9

I am quite interesting in using "call execute()" to simplify most procedure steps based on the above example. I review from the website that "call execute(argument)" is necessary. When I try to write the following step, a message "Statement is not valid or it is used out of proper order." listed in log. Please let me learn how to write "call execute()" to tackle my silly problem:

e.g. I want to set class1=student1,.......class10=student10,

data dummy;

set examine;

do m=1 to 10;

call execute("class" || m || "=student" || m || ";");

end;

run;

jones

Re: Iteration loops to macros

jakarman — Thu, 24 Jul 2014 06:06:31 GMT

The call execute is putting sas codelines into the interpreter that will be picked up after a processing boundary.

In this case AFTER the datastep his run, the new code picked up and it will see: class ... "=student ".... ' ;

Not really valid code as there is nothing else.

Re: Iteration loops to macros

JonesSmith — Thu, 24 Jul 2014 06:45:14 GMT

Hi Jaap,

Not quite understand. I attached my SAS log output. The new variables cannot be derived. In this case, how could I write this.

jones

Re: Iteration loops to macros

JonesSmith — Thu, 24 Jul 2014 06:49:32 GMT

Sorry, here is part of the SAS Log.

135 data dummy;
136     set examine;
137
138     put student1= student2= student3= student4= student5= student6= student7= student8= student9= student10=;
139
140     do m=1 to 10;
141        call execute("class" || strip(m) || "=student" || strip(m) || ";");
142     end;
143 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      141:37   141:63
student1=123 student2=80 student3=118 student4=193 student5=328 student6=239 student7=107 student8=200 student9=80 student10=60
NOTE: There were 1 observations read from the data set WORK.EXAMINE.
NOTE: The data set WORK.DUMMY has 1 observations and 11 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1   + class1=student1;
      ------
      180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
2   + class2=student2;
      ------
      180

Re: Iteration loops to macros

jakarman — Thu, 24 Jul 2014 07:02:46 GMT

Yep, as your read the log the data step is ready. When ready it gives the processing information that is aflter that line 143.
Than new code to process is coming in, it says where it is coming from (the execute statement). Getting no clue where that " + class1=student1; " should belong to.

There is no datastep running, no proc nothing. These assignments ..... where should they belong to? "out of proper order" is a good error description.

Re: Iteration loops to macros

JonesSmith — Thu, 24 Jul 2014 07:25:03 GMT

Hi Japp,

Physically from the program and log, student1-student10 have been actually input in the dataset and class1-class10 will be the newly created variables afterwards. But in the log, "+ class1=student1;" tells that it is used out of proper order. Based on the normal syntax, it is correct and can be interpreted. Not quite understand why it cannot be resolved. May I know how to manage it ?

jones

Re: Iteration loops to macros

jakarman — Thu, 24 Jul 2014 07:43:50 GMT

You cannot change code in your current executing datastep using subexec. T
hat code goes AFTER your datastep has been processed not in your datastep.

SAS (base language) is an interpreter with a lot freedom but due the nature of eliminating the row awareness it must know all columns before processing starts. That is coming back with the PDV (program data vector). Forget a lot of other languages (Cobol Pascal java C) as this is an essential difference. Some other 4-5 Gl language doing the similar like RPC.

What is your real issue? Think to solve it with:
- The power of SAS-macro processing is it can change code in a programmatic way before code analyses starts. Tat is before all of that so it can define some variables dynamic.

- using arrays and variable lists. Maybe overallocating the number of processed variables

Your description of what you want to do, is like that. SAS(R) 9.3 Language Reference: Concepts, Second Edition (arrays conceptual view)

- ?

Re: Iteration loops to macros

RW9 — Thu, 24 Jul 2014 08:00:56 GMT

Hi,

Am in meeting all day, so don't have much time. Call execute is a code generator. Basically you need to think about what you want as the final code, then you piece the information together into a string and that string is then sent out as code *after* your datastep code finishes. Therefore as Jaap has said, the code you generate if you just put it into a new file would not execute and give those errors. You need to build the full code:

data _null_;

call execute('data want; set have;'); /* send this line */

do I=1 to 10;

call execute('attrib avar'||strip(put(I,best.))||";"); /* Send a line with an attrib statement for each do loop */

end;

call execute('run;'); /* Finish the code */

run;

This will generate the code:

data want; set have;

attrib avar1;

attrib avar2;

...

run;

You can do a similar thing with macro loops, its basically different methods for the same thing. Both can be useful.

Re: Iteration loops to macros

JonesSmith — Thu, 24 Jul 2014 13:30:44 GMT

Many thanks to Jaap and RW9

jones