SAS macro: only first loop works

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SAS macro: only first loop works

Hi All,

I wrote a macro to loop through my data sets and generate macro variables to hold the first date and last date of each data set.

However, I found that the call symput function only works in the first loop and generates macro variables "min_&var1" and "max_&var1". Other macro variables that were supposed to be generated by this macro do not exist.

I ran "%put &&min_&var2", the log reads "Apparent symbolic reference MIN_ANDORRA not resolved". Anybody knows why?

Below is the macro:

%macro dates();

     %do i=1 %to &tot;

       proc sort data=&&var&i;by startdate;run;

       data _null_;

       set &&var&i end=eof;

       if _n_=1 then call symput("min_"||"&&var&i",put(startdate,date9.));

       if eof then call symput("max_"||"&&var&i",put(startdate,date9.));

       run;

     %end;

%mend dates;

%dates


Accepted Solutions
Solution
‎09-16-2013 07:21 AM
Super Contributor
Posts: 297

Re: SAS macro: only first loop works

If you want to use min_****** as a macro variable outside the macro you need to create it as a global macro variable.

Simply add %GLOBAL MIN_&&VAR&I.; before your proc sort.

This is because macro variables created in a macro are local and not global.  Placing a %PUT _USER_;  within the macro and one outside the macro will show you that the macro variables are created and no longer exist outside the macro.

OPTIONS MPRINT MLOGIC SYMBOLGEN;

DATA TEST TEST1 TEST2 TEST3 TEST4;

  FORMAT STARTDATE DATE9.;

  DO STARTDATE = "01JAN2010"d TO "01JAN2013"d;

  OUTPUT;

  END;

RUN;

DATA _NULL_;

  SET SASHELP.VTABLE (WHERE = (LIBNAME = 'WORK' AND MEMNAME ~= "_PRODSAVAIL")) END=LAST;

  COUNT+1;

  CALL SYMPUT("VAR"||TRIM(LEFT(PUT(COUNT,20.))),MEMNAME);

  IF LAST = 1 THEN CALL SYMPUT("TOT",TRIM(LEFT(PUT(COUNT,20.))));

RUN;

%PUT _USER_;

%MACRO DATES;

     %DO I=1 %TO &TOT.;

    %GLOBAL MIN_&&VAR&I.;

       PROC SORT DATA=&&VAR&I;

  BY STARTDATE;

    RUN;

       DATA _NULL_;

        SET &SYSLAST. END=EOF;

        IF _N_= 1 THEN CALL SYMPUT("MIN_"||"&&VAR&I",PUT(STARTDATE,DATE9.));

        IF EOF THEN CALL SYMPUT("MAX_"||"&&VAR&I",PUT(STARTDATE,DATE9.));

       RUN;

     %END;

  %PUT _USER_;

%MEND DATES;

%DATES;

%PUT _USER_;

I would however, suggest that there are better ways of doing what you are attempting, that would circumvent reading the entire file.

Regards,

Scott

View solution in original post


All Replies
Regular Contributor
Posts: 195

Re: SAS macro: only first loop works

proc sql noprint;

  select min(dates) into :min_date

  from have

  order by dates;

  select max(dates) into :max_date

  from have

  order by dates;

quit;

-Urvish

New Contributor
Posts: 4

Re: SAS macro: only first loop works

Hi Urvish,

Thanks for the codes! It works! It is more concise to use sql

Best,

Sean

Solution
‎09-16-2013 07:21 AM
Super Contributor
Posts: 297

Re: SAS macro: only first loop works

If you want to use min_****** as a macro variable outside the macro you need to create it as a global macro variable.

Simply add %GLOBAL MIN_&&VAR&I.; before your proc sort.

This is because macro variables created in a macro are local and not global.  Placing a %PUT _USER_;  within the macro and one outside the macro will show you that the macro variables are created and no longer exist outside the macro.

OPTIONS MPRINT MLOGIC SYMBOLGEN;

DATA TEST TEST1 TEST2 TEST3 TEST4;

  FORMAT STARTDATE DATE9.;

  DO STARTDATE = "01JAN2010"d TO "01JAN2013"d;

  OUTPUT;

  END;

RUN;

DATA _NULL_;

  SET SASHELP.VTABLE (WHERE = (LIBNAME = 'WORK' AND MEMNAME ~= "_PRODSAVAIL")) END=LAST;

  COUNT+1;

  CALL SYMPUT("VAR"||TRIM(LEFT(PUT(COUNT,20.))),MEMNAME);

  IF LAST = 1 THEN CALL SYMPUT("TOT",TRIM(LEFT(PUT(COUNT,20.))));

RUN;

%PUT _USER_;

%MACRO DATES;

     %DO I=1 %TO &TOT.;

    %GLOBAL MIN_&&VAR&I.;

       PROC SORT DATA=&&VAR&I;

  BY STARTDATE;

    RUN;

       DATA _NULL_;

        SET &SYSLAST. END=EOF;

        IF _N_= 1 THEN CALL SYMPUT("MIN_"||"&&VAR&I",PUT(STARTDATE,DATE9.));

        IF EOF THEN CALL SYMPUT("MAX_"||"&&VAR&I",PUT(STARTDATE,DATE9.));

       RUN;

     %END;

  %PUT _USER_;

%MEND DATES;

%DATES;

%PUT _USER_;

I would however, suggest that there are better ways of doing what you are attempting, that would circumvent reading the entire file.

Regards,

Scott

New Contributor
Posts: 4

Re: SAS macro: only first loop works

Hi Scott,

Yes you are right. I forgot the scope of the macro variable. I should have created those variables as global macro variables.

Thanks for the support!

Best,

Sean

Super Contributor
Posts: 282

Re: SAS macro: only first loop works

Hi,

Like @Scott_Mitchell said you are encountering issues with the scope of the macro variable. You might also want to check out the call sumputx routine which allows you to specify that the macro variable is created in the Global symbol table:

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

If you want to use something like 's solution, then you can also consider the following form:

proc sql noprint;

  select min(age), max(age) into :min_age, :max_age

  from sashelp.class;

quit;

Regards,

Amir.

New Contributor
Posts: 4

Re: SAS macro: only first loop works

Hi, Amir,

Thank you for the info! It seems that the call symputx is a better choice. Actually, the trailing blanks messed up those macro variables the first time I tested the code. I did an extra step to remove those blanks. Now the call symputx can do all in one step.

Best,

Sean

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 283 views
  • 10 likes
  • 4 in conversation