macro for proc sql select into, good for i=1 but not i=2

Question

I was trying to use proc sql select into to generate macro variables, and I would like to do it for all variables in the code dataset, so I embedded it within a macro. The proc sql runs ok for both cd_1 and cd_2 as there are valid print out. However, only cd_1 has the value I wanted, and cd_2 was not resolved. I have pasted the full code below.

data code;
infile datalines delimiter=',';
input MUSCLE $ STIMULANTSFL $ ;
datalines;
baclofen,amphetamine
carisoprodol,dexmethylphenidate
;
run;

*Selecting all the variables names;
proc sql ;
select name into : vars1 - :vars2  from dictionary.columns
where LIBNAME = 'WORK' and MEMNAME = 'CODE';
quit;
*for all names select the codes;
%macro getcode; 
%do i=1 %to 2;
PROC SQL ; 
    select (trim(&&vars&i.)) into : cd_&i. separated by '|' from code where (trim(&&vars&i.)) ne '';
quit;
%end;
%mend;
%getcode;%put &cd_1;%put &cd_2;

Answer 1

Macro variable scope issue - local versus global. I suspect your first one only works because you tested it. Move the %PUT to inside the macro to have it resolve. Your macro variables do not exist outside your macro unless you explicitly set them to exist.

Add %GLOBAL to create global macro variables.

data code;
    infile datalines delimiter=',';
    input MUSCLE $ STIMULANTSFL $;
    datalines;
baclofen,amphetamine
carisoprodol,dexmethylphenidate
;
run;

*Selecting all the variables names;

proc sql;
    select name into : vars1 -   from dictionary.columns where LIBNAME='WORK' and 
        MEMNAME='CODE';
quit;

option mprint;
*for all names select the codes;

%macro getcode;
    %do i=1 %to 2;
        %global cd_&i;
        PROC SQL;
            select (trim(&&vars&i.)) into : cd_&i. separated by '|' from code 
                where (trim(&&vars&i.)) ne '';
        quit;

        %put cd_&i.;
    %end;
%mend;

%getcode;

 %put &cd_1.;
 %put &cd_2.;