topic Re: Why macro var created from SQL can't be used immediately in array? in SAS Programming

Why macro var created from SQL can't be used immediately in array?

jiangmi — Fri, 13 Feb 2015 15:40:17 GMT

Hi, All,

In this case, &num was created by SQL to count the number of obs in data(have).

Instead using &num directly to create array, I have to assign the &num value to &end to make it work. Why?

The log screen was attached.

Thanks.

Joe

data have;

input year_mth @@;

cards;

201412 201411 201410

;run;

proc sql;

select count (distinct year_mth) into:num

from have

;quit;

%put check the new macro value: &num;

/*Why not working if use &num directly*/

data notwork;

array L (*) L01-L&num;

do i=01 to &num;

L(i)=i;

end;

run;

/*It works when converting by let statement*/

%let end = &num;

data yeswork;

array L (*) L01-L&end;

do i=01 to &end;

L(i)=i;

end;

run;

Re: Why macro var created from SQL can't be used immediately in array?

PaigeMiller — Fri, 13 Feb 2015 15:49:10 GMT

Try this:

proc sql;

select count (distinct year_mth) into:num trimmed

from have

;

Re: Why macro var created from SQL can't be used immediately in array?

Reeza — Fri, 13 Feb 2015 15:50:27 GMT

Because the macro variable has leading spaces so what the compiler sees is:

L. 3

Use the Trimmed option in your SQL to remove the spaces:

...

Into :num Trimmed

from ...

Re: Why macro var created from SQL can't be used immediately in array?

jiangmi — Fri, 13 Feb 2015 16:30:55 GMT

I didn't realized that modification in sql.

Thanks.

Joe