topic Re: Proc SQL save multiple value to macro variable in SAS Programming

Proc SQL save multiple value to macro variable

hhchenfx — Mon, 14 Sep 2020 22:03:02 GMT

Hi Everyone,

I copied a code from SAS book and somehow it is not working.

The idea is to save all Name in a macro variable and print it one by one.

Can you please help?

Thanks,

Harry

proc sql noprint;
select name into :namelist1-
from sashelp.class
; quit;

%macro doprint;
	%do i=1 %to &sqlobs;
		%put name &i is &namelist1&i;
	%end;
%mend;

Re: Proc SQL save multiple value to macro variable

PaigeMiller — Mon, 14 Sep 2020 22:45:21 GMT

%macro doprint;
	%do i=1 %to &sqlobs;
		%put name &i is &&namelist&i;
	%end;
%mend;
%doprint

But I must ask, why do you need macro variables and macro do loops for this? Why not simply this:

proc sql;
select name from sashelp.class
; quit;

which is soooooo much easier than printing via a macro do loop.

Or why not simply this:

proc print data=sashelp.class;
var name;
run;

which is soooooo much easier than printing via a macro do loop.

You should not resort to macros just to print data that is already in a SAS data set, this is completely unnecessary and counter-productive.

Re: Proc SQL save multiple value to macro variable

hhchenfx — Tue, 15 Sep 2020 00:35:43 GMT

Hi Paige,

I am learning that Macro code from SAS book.

But still, you code only give me the first row (i=1).

Thanks,

Harry

Re: Proc SQL save multiple value to macro variable

jimbarbour — Tue, 15 Sep 2020 01:09:48 GMT

@hhchenfx,

I just tried @PaigeMiller's code, and it seems to be working for me. Make sure you run both steps together, one right after another. Maybe you could just cut and paste the follwing code into an editor window:

proc sql noprint;
	select name into :namelist1 - 
		from sashelp.class;
quit;

%macro doprint;
	%do i=1 %to &sqlobs;
		%put name &i is &&namelist&i;
	%end;
%mend;

%DoPrint;

Make sure that "namelist" in "&&namelist&i" is spelled correctly. Originally, you spelled it "namelist1" which will not work properly.

Jim

Re: Proc SQL save multiple value to macro variable

hhchenfx — Tue, 15 Sep 2020 03:17:17 GMT

Thank you for helping.

I got it!

Harry