BookmarkSubscribeRSS Feed
wongmay
Calcite | Level 5

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

15 REPLIES 15
Tom
Super User Tom
Super User

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;

wongmay
Calcite | Level 5

Great help !  Thanks.

regards

may

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

wongmay
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

wongmay
Calcite | Level 5

Work fine ! Thank you.

regards

may

JonesSmith
Calcite | Level 5

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

jakarman
Barite | Level 11

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.  

---->-- ja karman --<-----
JonesSmith
Calcite | Level 5

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

JonesSmith
Calcite | Level 5

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

jakarman
Barite | Level 11

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.  

---->-- ja karman --<-----
JonesSmith
Calcite | Level 5

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

jakarman
Barite | Level 11

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)

- ?

---->-- ja karman --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 1717 views
  • 0 likes
  • 5 in conversation