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
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.
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.
Many thanks!
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.
This's cool. Many thanks
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.
Cool! Many thanks!
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.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.