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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
Astounding
PROC Star

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.

Sunny_Sun
Calcite | Level 5

Many thanks!

Peter_C
Rhodochrosite | Level 12

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.

Sunny_Sun
Calcite | Level 5

This's cool. Many thanks

Tom
Super User Tom
Super User

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.

Sunny_Sun
Calcite | Level 5

Cool! Many thanks!

art297
Opal | Level 21

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.

Sunny_Sun
Calcite | Level 5

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.

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 9 replies
  • 1297 views
  • 7 likes
  • 6 in conversation