topic Multiple macro variable: PROC SQL select into does not work as expected in SAS Programming

Multiple macro variable: PROC SQL select into does not work as expected

Cecillia_Mao — Mon, 10 Aug 2020 16:08:44 GMT

Hi SAS experts,

For the below codes, I don't understand why &varlist only catched “height”, and low only catched '1'. It supposed to be "height weight" for &varlist; and "1.0 25" for &lowlist. Any thoughts would be appreciated!

Thanks in advance~

data a;
	input var $ low high;
	cards;
height 1.0 2.0
weight 25  200
;
run;
proc sql;
	select Var, low, high into:varlist,:lowlist,:highlist separated by ' '
	from a;
quit;
%put varlist: &varlist ---
	 lowlist: &lowlist ---
	 highlist:&highlist;

Re: Multiple macro variable: PROC SQL select into does not work as expected

Reeza — Mon, 10 Aug 2020 16:20:21 GMT

Note that it does work for the last entry.

The portion of code " separated by ' ' " is only applied to the last set of items which is why that one shows correctly. Specify it for every list if you want to store multiple items in each list, otherwise it stores only the first value.

This works.

proc sql;
	select Var, low, high into
	:varlist separated by " ",
	:lowlist separated by " ",
	:highlist separated by ' '
	from a;
quit;
%put varlist: &varlist ---
	 lowlist: &lowlist ---
	 highlist:&highlist;

@Cecillia_Mao wrote:

Hi SAS experts,

For the below codes, I don't understand why &varlist only catched “height”, and low only catched '1'. It supposed to be "height weight" for &varlist; and "1.0 25" for &lowlist. Any thoughts would be appreciated!

Thanks in advance~
data a;
	input var $ low high;
	cards;
height 1.0 2.0
weight 25  200
;
run;
proc sql;
	select Var, low, high into:varlist,:lowlist,:highlist separated by ' '
	from a;
quit;
%put varlist: &varlist ---
	 lowlist: &lowlist ---
	 highlist:&highlist;

Re: Multiple macro variable: PROC SQL select into does not work as expected

Cecillia_Mao — Tue, 11 Aug 2020 01:17:46 GMT

Thank you! That works perfectly!