CODEWITHSUNDEEP
sas
G_V
G_V

Reputation: 13

Creating suffixes while iterating through list

I am stuck trying to iterate through a list of values in a macro %do loop. Each value is supposed to be used as a variable suffix.

The approach is based on SAS documentation: http://support.sas.com/kb/26/155.html

The (simplified) code is:

%macro loop(values);                        
     %let count=%sysfunc(countw(&values));
     %do i = 1 %to &count;          
      %let value=%qscan(values,i,%str(,));
        proc sql;
            select count(distinct hut_id) as prefix_&value.
            from saslib.tl1_results_eval
            group by plan_cell;
        quit;
     %end;                                                                             
%mend;                   
%loop(%str(a,b,c,d))

The resulting error message is:

MLOGIC(LOOP):  %DO loop beginning; index variable I; start value is 1; stop value is 4; by value
      is 1.
MLOGIC(LOOP):  %LET (variable name is VALUE)
MPRINT(LOOP):   proc sql;
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and
              COLUMN where the error has occurred.
ERROR 22-322: Syntax error, expecting one of the following: ',', AS.
MPRINT(LOOP):   select count(distinct hut_id) as prefix_a from group by plan_cell;
MPRINT(LOOP):   quit;

Interestingly enough, if I remove "prefix_" from "count(distinct hut_id) as prefix_&value." - it works absolutely fine. Unfortunately, I do need the prefix there. Also, as you can see in the log message, the PROC SQL statement does get compiled correctly in the end, but the code errors out before getting executed.

Any ideas what's happening here? Thank you!

Upvotes: 1

Views: 399

Answers (1)

Allan Bowe
Allan Bowe

Reputation: 12691

You are inserting invisible quoting characters due to the qscan function. Try:

  %macro loop(values);                   
    proc sql;     
    %let count=%sysfunc(countw(&values));
    %do i = 1 %to &count;          
      %let value=%qscan(values,i,%str(,));
      select count(distinct hut_id) as prefix_%unquote(&value)
        from saslib.tl1_results_eval
        group by plan_cell;
    %end;  
    quit;                                                               
  %mend;                   
  %loop(%str(a,b,c,d))

Note the movement of the proc sql; statement - there are few good reasons to quit; and reinstantiate the SQL procedure between each SQL statement, which incurs overhead..

Upvotes: 1

Related Questions