BookmarkSubscribeRSS Feed
afiqcjohari
Quartz | Level 8

I want to determine in advance the number of distinct items and create as many macro variables as the number of items.

 

*get the number of distinct cPOSMode;
proc sql;
	select count(distinct cPOSMode) into :cPOSModeCNT
	from have;
quit;

*generate the macro variables based on the previous number;
proc sql;
 select distinct cPOSMode 
 into :cPOSMode1-:cPOSMode&cPOSModeCNT.
 from have;
 quit;

Any tips on this?

12 REPLIES 12
gamotte
Rhodochrosite | Level 12

Hello,

 

Use a data step :

 

data _NULL_;
    set have;
    call symput(cats("cPosMode",_N_),cPosMode);
    call symput("cPosModeCNT",_N_);
run;

Edit : use a prior proc sort with nodupkey to eliminate duplicates.

Kurt_Bremser
Super User

See my anser in the other thread:

*get the number of distinct cPOSMode;
proc sql;
	select count(distinct cPOSMode) into :cPOSModeCNT
	from have;
quit;

%let cPOSModeCNT=%sysfunc(strip(&cPOSModeCNT));

*generate the macro variables based on the previous number;
proc sql;
 select distinct cPOSMode 
 into :cPOSMode1-:cPOSMode&cPOSModeCNT.
 from have;
 quit;

The select into formats the number, causing leading blanks that have to be removed.

GPNaveen
Fluorite | Level 6

Hello,

 

Before we use a macro value obtained from SQL we need to trim the blanks.

That is, Macro Variable : cPOSModeCNT would have leading and trailing blanks which needs to be taken off before we use it alongside a literal.

 

The code should work with this small change as below:

 

*get the number of distinct cPOSMode;
proc sql;
	select count(distinct cPOSMode) into :cPOSModeCNT
	from have;
quit;

%let cPOSModeCNT=&cPOSModeCNT;
*generate the macro variables based on the previous number; proc sql; select distinct cPOSMode into :cPOSMode1-:cPOSMode&cPOSModeCNT. from have; quit;

I hope this would be helpful.

Thanks

GP 

afiqcjohari
Quartz | Level 8
New knowledge on that automatic trimming and the fact that the macro has leading and trailing blanks built in. I wish my company starts using other modern languages 🙂
Kurt_Bremser
Super User

But the question still remains: what will be done with that macro variable list?

In >90% of cases, this is better handled with by-group processing. And if one needs to iterate through a list of values, keeping them in a dataset and using call execute from that will be the option of choice.

The less macro logic you need to employ in your code, the better. Data step code is much easier to maintain than macro code.

afiqcjohari
Quartz | Level 8
@Kurt_Bremser, What do you mean by by-group processing? Are you referring to the executing the different values from a dataset?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I have to agree with @Kurt_Bremser here.  This is something we see on these forums on a day to day basis, and in work all the time as well.  Macro language is not a replacement for Base SAS programming.  When you are finding that you are writing lots of macro code, then you are simply creating work for yourself and making unmanageable code.  Lets examine your example.  How many cposmodecnt's are we talking about, with a big dataset this could run to millions.  Are they all going to valid for macro paratmeters, i.e. conform to SAS naming conventions, what happens if there are globla macro variables setup for these - as you have no control over what is created there, what about other macro processes?  

In all cases there is far simpler base code which will achieve the same result - as remember macro does nothing, it just creates some base code - utilise data modelling techinques e.g. re-structure your data, use inbuilt base SAS by group processing, use inbuilt variable lists or arrays etc.

Whilst i understand that the above advice will be ignored, just remember that one day you will be the person coming into a job and seeing this, and for me this would be a delete key jobbie.

afiqcjohari
Quartz | Level 8
I'm open to the workflow ideas as I'm new to SAS, having abandoned it for years but the current job is using a legacy system which has SAS on top of it.

I only see macro language as the medium for me to give some modularity to the work. Though I maybe wrong as I'm not that aware of things like what is a valid macro parameters for example.

Having said that, if you have something for me to read on best practices to write SAS code, I'd be very interested to learn more.
Ksharp
Super User
proc sql;
	select count(distinct cPOSMode) into :cPOSModeCNT  separated by ' '
	from have;

 select distinct cPOSMode 
 into :cPOSMode1-:cPOSMode&cPOSModeCNT.
 from have;
 quit;

Tom
Super User Tom
Super User

You do not need to count the number in advance. Let SAS count for you if you need the count.

proc sql noprint;
  select distinct cPOSMode 
    into :cPOSMode1-
    from have
  ;
%let cPOSModeCNT=&sqlobs ;
quit;

If you are using an old version of SAS then just give it a upper bound that is larger than any value you will encounter.

    into :cPOSMode1-:cPOSMode99999

As to code to eliminate generating macro "arrays" it just depends on what you are actually doing. Some things can easily be done using BY statement.

proc summary data=have nway ;
   class cPOSMOde ;
....

Or pernaps:

data xx ;
  set have ;
  by cPOSmode ;
  if first.cPOSMode .....
  ...
  if last.cPOSMode ....
run;

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!

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
  • 12 replies
  • 1997 views
  • 4 likes
  • 7 in conversation