BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pawan
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RichardinOz
Quartz | Level 8

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

8 REPLIES 8
shivas
Pyrite | Level 9

Hi,

Just try to assign like this.

%let nn=&n;

retain form_1-form_&nn;

Thanks,

Shiva

RichardinOz
Quartz | Level 8

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

Haikuo
Onyx | Level 15

or less wording:

select cats(value) into :n

Haikuo

pawan
Obsidian | Level 7

Thanks Richard, it worked.

MagnusMengelbier
Calcite | Level 5

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

pawan
Obsidian | Level 7

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

MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Ksharp
Super User

one more way.

RETAIN FORM_1 - FORM_%left(&n)   ;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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