proc sql select into with do loop

[CGregg]

I am trying to loop through a list of values in the where clause and select into a var. only retaining last value.

%macro Test (data, varlist);

%let dsid = %sysfunc(open(&data));

%let nobs = %sysfunc(attrn(&dsid,nlobs));

%let dsid = %sysfunc(close(&dsid));

%let nbrwords =  %sysfunc(countw(&varlist));

    %put &nbrwords.;

%do i = 1 %to &nbrwords;

    %let dsname = %scan(&varlist,&i,%str( ));;

    %let ds = %scan(&data, -1, '.');

    %let ln = %scan(&data, +1, '.');

PROC SQL;

SELECT name into :numlist separated by ','

FROM dictionary.columns

WHERE  type eq "num" and libname eq "TESTDATA" and memname eq "TEST_DATASET1" and name eq %upcase("&dsname")

;

QUIT;

%end;

   %put &numlist;

%mend Test;

%Test(testdata.test_dataset1,height weight);

[Tom]

I am having a hard time relating the question to the code you posted.

The code kind of looks like it might be trying to find overlap between the set of variables in a dataset (&DATA) and the set of variables in a delimited string (&VARLIST).

So if you had DATA=SASHELP.CLASS and VARLIST= Name AGE RACE the result would be NAME AGE since they appear in SASHELP.CLASS and RACE does not.

Or perhaps since you seem to only want numeric variables perhaps the result is just AGE since NAME is a character variable?

%macro test(data,varlist);
%local libname memname numlist ;
%let memname=%upcase(%scan(&data,-1,.));
%let libname=%upcase(%scan(work.&data,-2,.));

proc sql noprint;
 select name 
   into :numlist separated by ' '
 from dictionary.columns
 where libname="&libname" and memname="&memname"
   and type='num'
   and findw("&varlist",trim(name),' ','i')
 ;
quit;
%put &=sqlobs &=numlist;
%mend test;

18   %test(sashelp.class,name age race);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


SQLOBS=1 NUMLIST=Age

Is that what you are trying to do?

If not then please explain what you are trying to do.

[CGregg]

In a macro I am trying to loop though a list of variables and those that are numeric columns . I want to put the name of the column in a new list of values. Example sashelp.class In: name, age, height, weight out: numlist= age, height, weight

[Tom]

---

@CGregg wrote:
In a macro I am trying to loop though a list of variables and those that are numeric columns . I want to put the name of the column in a new list of values. Example sashelp.class In: name, age, height, weight out: numlist= age, height, weight

---

In that case the code I posted should work.  If for some strange reason you actually want commas in the string just change the SEPARATED BY clause of the INTO part of the SQL query.  Perhaps you need to use the list in a SQL code instead of SAS code?  

Note if you want to use the macro variable after the macro ends you will need to NOT make it LOCAL.  But in that case you should probably modify the macro to accept the name for the macro variable.  Make sure to test if the named macro variable already exists before trying to make it GLOBAL to avoid errors.

%macro numvars(data,varlist,mvar=numlist);
%local libname memname ;
%if not %symexist(&mvar) %then %global &mvar;
%let memname=%upcase(%scan(&data,-1,.));
%let libname=%upcase(%scan(work.&data,-2,.));

proc sql noprint;
 select name 
   into :&mvar separated by ' '
 from dictionary.columns
 where libname="&libname" and memname="&memname"
   and type='num'
   and findw("&varlist",name,',|','sitr')
 ;
quit;
%put &=sqlobs &&&mvar;
%mend numvars;

%numvars(sashelp.class,name age race);
%numvars(sashelp.cars,length|cylinders|model);

Note if the dataset was created with VALIDVARNAME= option set to ANY then you probably will want to use the NLITERAL() function in the SELECT so the generated macro variable will contain name literals when the names do not conform to SAS name rules.

select nliteral(name) into ...

[CGregg]

Thank you for your help. I am using this macro to pass var to other macros. Is it possible to keep the same varlist order in the output(mvar)? It is currently sorting in alphabetical order.

[Tom]

Which order do you want?

The order they actually appear in the dataset?

%macro numvars(data,varlist,mvar=numlist);
%local libname memname ;
%if not %symexist(&mvar) %then %global &mvar;
%let memname=%upcase(%scan(&data,-1,.));
%let libname=%upcase(%scan(work.&data,-2,.));

proc sql noprint;
 select name 
   into :&mvar separated by ' '
 from dictionary.columns
 where libname="&libname" and memname="&memname"
   and type='num'
   and findw("&varlist",name,',|','sitr')
order by varnum
 ;
quit;
%put &=sqlobs &&&mvar;
%mend numvars;

The order they appear in the input parameter?

  order by findw("&varlist",name,',|','sitr')

Something else?

[CGregg]

That did it. I never knew you could use findw in a where clause. Thanks

[Ksharp]

%macro numvars(data,varlist);
%global numlist;
%let numlist=;
proc sql noprint;
 select name into :list separated by ' '
 from dictionary.columns
 where libname="%upcase(%scan(&data,1,.))" and memname="%upcase(%scan(&data,-1,.))" and type='num';
quit; 
%do i=1 %to %sysfunc(countw(&varlist));
 %let temp=%scan(&varlist,&i); 
 %if %sysfunc(findw(%upcase(&list),%upcase(&temp))) %then %let numlist=&numlist &temp;
%end;
%mend numvars;

%numvars(sashelp.class,name weight height age race);

%put &=numlist ;

233  %macro numvars(data,varlist);
234  %global numlist;
235  %let numlist=;
236  proc sql noprint;
237   select name into :list separated by ' '
238   from dictionary.columns
239   where libname="%upcase(%scan(&data,1,.))" and memname="%upcase(%scan(&data,-1,.))"
239! and type='num';
240  quit;
241  %do i=1 %to %sysfunc(countw(&varlist));
242   %let temp=%scan(&varlist,&i);
243   %if %sysfunc(findw(%upcase(&list),%upcase(&temp))) %then %let numlist=&numlist &temp
243! ;
244  %end;
245  %mend numvars;
246
247  %numvars(sashelp.class,name weight height age race);
NOTE: “PROCEDURE SQL”所用时间（总处理时间）:
      实际时间          0.00 秒
      CPU 时间          0.00 秒


248
249  %put &=numlist ;
NUMLIST=weight height age