DATA Step, Macro, Functions and more

Need help on macro variable created from into: clause

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Need help on macro variable created from into: clause

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.


Accepted Solutions
Solution
‎11-15-2012 07:52 AM
Super Contributor
Posts: 644

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

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

View solution in original post


All Replies
Super Contributor
Posts: 349

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

Hi,

Just try to assign like this.

%let nn=&n;

retain form_1-form_&nn;

Thanks,

Shiva

Solution
‎11-15-2012 07:52 AM
Super Contributor
Posts: 644

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

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

Respected Advisor
Posts: 3,124

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

or less wording:

select cats(value) into :n

Haikuo

Contributor
Posts: 37

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

Thanks Richard, it worked.

New Contributor
Posts: 3

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

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 ;

37

38   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

39

40   %put [&ex1];

[      16]

41   %put [&ex2];

[16]

42   %put [&ex3];

[16

   ]

43   %put [&ex4];

[16      ]

HTH

Contributor
Posts: 37

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

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

Trusted Advisor
Posts: 1,248

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

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

Super User
Posts: 9,681

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

one more way.

RETAIN FORM_1 - FORM_%left(&n)   ;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 819 views
  • 12 likes
  • 7 in conversation