topic Put all values of a variable into macro variable using proc sql into but it reads only first value in SAS Enterprise Guide

Put all values of a variable into macro variable using proc sql into but it reads only first value

alepage — Tue, 06 Feb 2024 20:51:26 GMT

Hello,

I am using the script below to put all the values into macro variables but it reads only the first value. Why?

How to solve that Issue.

Vartable 3 first observations only

name1                            name2
ps.startDate,	pt.startDate = ps.startDate,
ps.endDate,	pt.endDate = ps.endDate,
ps.status,	pt.status = ps.status,

proc sql noprint ;
   select strip(name1), strip(name2)
      into :varlist1, :varlist2  
      from vartable;
Quit;
%put &=varlist1.;
%put &=varlist2.;

Re: Put all values of a variable into macro variable using proc sql into but it reads only first val

Tom — Tue, 06 Feb 2024 21:21:42 GMT

Because that is what you asked it to do.

You can either concatenate the values into single macro variables by using the SEPARATED BY keyword. (PS Removing the leading spaces changes the meaning of the values. Just remove the trailing spaces.)

proc sql noprint ;
   select trim(name1), trim(name2)
      into :varlist1 separated by '|'
         , :varlist2 separated by '|'
      from vartable
;
%let nobs=&sqlobs;
quit;
%put &=varlist1;
%put &=varlist2;

Or you can create two SETS of variables with numeric suffixes.

proc sql noprint ;
   select trim(name1), trim(name2)
      into :varlist1_1 -
         , :varlist2_1 -
      from vartable
;
%let nobs=&sqlobs;
quit;
%put &=varlist1_1  varlist1_&nobs=&&varlist1_&nobs;
%put &=varlist2_1  varlist2_&nobs=&&varlist2_&nobs;

Re: Put all values of a variable into macro variable using proc sql into but it reads only first val

Astounding — Tue, 06 Feb 2024 21:22:52 GMT

When you fail to add "separated by", SAS thinks you only want one value.