Help using Base SAS procedures

define macro variables by proc sql into

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

define macro variables by proc sql into

Hi,

I am trying to store some values from a data set to a series of macro variables. Eg. the data is called temp, variable of interest is called name.

proc sql noprint;

      select count(*)   

          into: inn_nvar1

      from temp;


      select distinct name

          into :inxvar1 - :inxvar&inn_nvar1.

             from temp;

quit;


That is, I need to first store # of unique values of name, and then store each of them into macro variable of inxvar. However, I am keeping getting errors due to the use of &inn_nvar1. in second into. If I use accurate numbers, such as, inn_nvar1=39 and into :inxvar1 - :inxvar39. then there is no any errors.

How can I still let the number of inxvar depends on inn_nvar1?

Thanks,

Sunny


Accepted Solutions
Solution
‎03-06-2015 05:23 PM
Super User
Posts: 5,511

Re: define macro variables by proc sql into

Posted in reply to Sunny_Sun

When SQL performs a numeric to character conversion (as it must, in order to store a numeric value in a macro variable), it creates leading blanks.  Just add between the two SELECT statements:

%let inn_nvar1 = &inn_nvar1;

That will remove any leading blanks.

View solution in original post


All Replies
Solution
‎03-06-2015 05:23 PM
Super User
Posts: 5,511

Re: define macro variables by proc sql into

Posted in reply to Sunny_Sun

When SQL performs a numeric to character conversion (as it must, in order to store a numeric value in a macro variable), it creates leading blanks.  Just add between the two SELECT statements:

%let inn_nvar1 = &inn_nvar1;

That will remove any leading blanks.

Contributor
Posts: 33

Re: define macro variables by proc sql into

Posted in reply to Astounding

Many thanks!

Valued Guide
Posts: 2,177

Re: define macro variables by proc sql into

Posted in reply to Sunny_Sun

It is easier than you expect

proc sql noprint;

select distinct name

      into :inxvar1 - :inxvar99999

       from temp;

%let N_names = &sqlobs ;

quit;

 

only as many macro cariables as neccessary are created.

Beware that these macro variables might have trailing blanks.

Contributor
Posts: 33

Re: define macro variables by proc sql into

This's cool. Many thanks

Super User
Super User
Posts: 7,055

Re: define macro variables by proc sql into

Posted in reply to Sunny_Sun

You do not need the first query.  Also you can use the automatic variable SQLOBS to find out how many where found.

proc sql noprint;

select distinct name

  into :inxvar1 -

  from temp

;

%let inxvarN = &sqlobs ;

quit;

If you are using an older version of SAS you will need to set an explicit upper bound for the range of variable names (:inxvar1 - :inxvar99999 ). 

SQL will only create enough variables to match the number of observations returned from the query.

Contributor
Posts: 33

Re: define macro variables by proc sql into

Cool! Many thanks!

PROC Star
Posts: 7,474

Re: define macro variables by proc sql into

Posted in reply to Sunny_Sun

You've already gotten a number of good answers but, I have to ask, how to you plan to use the macro variables? Depending upon how you answer that question, it might end up being easier if you simply put all of the names in one macro variable.

Contributor
Posts: 33

Re: define macro variables by proc sql into

I need the count & each macro variable for later uses, separately. These are all great answers and very much helpful.  Thanks for asking and have a nice day.

Super User
Posts: 10,035

Re: define macro variables by proc sql into

Posted in reply to Sunny_Sun

Two ways :

1)

proc sql noprint;

      select count(*)  

          into : inn_nvar1 separated by ' '

      from temp;


      select distinct name

          into :inxvar1 - :inxvar&inn_nvar1.

             from temp;

quit;


2)

proc sql noprint;

      select count(*)  

          into: inn_nvar1

      from temp;


      select distinct name

          into :inxvar1 - :inxvar%left(&inn_nvar1)

             from temp;

quit;

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 310 views
  • 7 likes
  • 6 in conversation