topic Re: define macro variables by proc sql into in SAS Procedures

define macro variables by proc sql into

Sunny_Sun — Fri, 06 Mar 2015 21:20:31 GMT

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

Re: define macro variables by proc sql into

Astounding — Fri, 06 Mar 2015 22:23:27 GMT

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.

Re: define macro variables by proc sql into

Peter_C — Fri, 06 Mar 2015 22:24:55 GMT

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.

Re: define macro variables by proc sql into

Tom — Fri, 06 Mar 2015 23:52:21 GMT

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.

Re: define macro variables by proc sql into

art297 — Sat, 07 Mar 2015 00:08:33 GMT

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.

Re: define macro variables by proc sql into

Sunny_Sun — Mon, 09 Mar 2015 13:15:35 GMT

Many thanks!

Re: define macro variables by proc sql into

Sunny_Sun — Mon, 09 Mar 2015 13:16:57 GMT

This's cool. Many thanks

Re: define macro variables by proc sql into

Sunny_Sun — Mon, 09 Mar 2015 13:18:20 GMT

Cool! Many thanks!

Re: define macro variables by proc sql into

Sunny_Sun — Mon, 09 Mar 2015 13:19:56 GMT

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.

Re: define macro variables by proc sql into

Ksharp — Tue, 10 Mar 2015 09:24:22 GMT

Two ways :

proc sql noprint;

select count(*)

into : inn_nvar1 separated by ' '

from temp;

select distinct name

into :inxvar1 - :inxvar&inn_nvar1.

from temp;

quit;

proc sql noprint;

select count(*)

into: inn_nvar1

from temp;

select distinct name

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

from temp;

quit;