topic Re: Need help on macro variable created from into: clause in SAS Programming

Need help on macro variable created from into: clause

pawan — Thu, 15 Nov 2012 12:22:23 GMT

Hi SASians,

I've a macro variable created with an into: clause from a max(count(*)) function using PROC SQL. When I invoke the same variable for a variable list decleration I end up with error messages as below

SYMBOLGEN: Macro variable N resolves to 10

705

ERROR: Missing numeric suffix on a numbered variable list (FORM_1-FORM_).

Code used for macro var creation is

Proc SQL;

SELECT max(maxi) into : n from

( select id_var, count(*) as maxi from prim_tab);

QUIT;

The above variable is invoked in a retain statement as below

RETAIN FORM_1 - FORM_&n;

It looks like the macro var is generated with spaces to the left and hence the above error. Usually Count function should retrieve numeric datatype values and this shouldnt be an issue. But as I'm surprised to find such error. Any solutions and support will be highly appreciated.

Re: Need help on macro variable created from into: clause

shivas — Thu, 15 Nov 2012 12:39:47 GMT

Hi,

Just try to assign like this.

%let nn=&n;

retain form_1-form_&nn;

Thanks,

Shiva

Re: Need help on macro variable created from into: clause

RichardinOz — Thu, 15 Nov 2012 12:52:39 GMT

SAS does an automatic number to character conversion using BEST12 format. That gives you the leading blanks.

You can emulate trimming by just writing

%let n = &n ;

In this case SAS will skip the multiple blanks in the resolved value and store it in a macro variable with the same name.

Or you can be a purist and in your SQL have

select trim(left(put(value, best.))) into :n

Richard in Oz

Re: Need help on macro variable created from into: clause

Haikuo — Thu, 15 Nov 2012 14:08:36 GMT

or less wording:

select cats(value) into :n

Haikuo

Re: Need help on macro variable created from into: clause

pawan — Thu, 15 Nov 2012 15:25:22 GMT

Thanks Richard, it worked.

Re: Need help on macro variable created from into: clause

MagnusMengelbier — Thu, 15 Nov 2012 16:52:53 GMT

A trick can be to use separated by to automatically trim the value, which will work regardless of a numeric or character value.

SELECT max(maxi) into : n separated by ' ' from

( select id_var, count(*) as maxi from prim_tab);

QUIT;

And following onto Richards example, strip() would work just as well. Regardless, it may not be a bad idea to use a put() statement to control the data value format being stored in the macro variable. Have attached some examples as the effect is interesting and all the extra spaces in ex3 is not a typo. .

proc sql noprint;

select max(age) into: ex1 from sashelp.class ;

select max(age) into: ex2 separated by ' ' from sashelp.class ;

select cats(max(age)) into: ex3 from sashelp.class ;

select put(max(age), 8.-L) into: ex4 from sashelp.class ;

quit;

%put [&ex1];

%put [&ex2];

%put [&ex3];

%put [&ex4];

Would give the following in the log

32 proc sql noprint;

33 select max(age) into: ex1 from sashelp.class ;

34 select max(age) into: ex2 separated by ' ' from sashelp.class ;

35 select cats(max(age)) into: ex3 from sashelp.class ;

36 select put(max(age), 8.-L) into: ex4 from sashelp.class ;

38 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

40 %put [&ex1];

[ 16]

41 %put [&ex2];

[16]

42 %put [&ex3];

[16

]

43 %put [&ex4];

[16 ]

HTH

Re: Need help on macro variable created from into: clause

pawan — Thu, 15 Nov 2012 18:04:27 GMT

Oooo!! Lovely show!! Thanks a lot... and thanks all..

Re: Need help on macro variable created from into: clause

MichelleHomes — Thu, 15 Nov 2012 19:11:26 GMT

And if you are using SAS 9.3 you can use the open-ended macro range technique as described in Chris Hemedinger's blog post... Improving on a SAS programming pattern - The SAS Dummy

Using this technique eliminates the need for the first SQL query to get the number of observations into a macro variable as you don't need to in SAS 9.3 and the macro variable values are trimmed as well.

Cheers,

Michelle

Re: Need help on macro variable created from into: clause

Ksharp — Fri, 16 Nov 2012 03:16:49 GMT

one more way.

RETAIN FORM_1 - FORM_%left(&n) ;