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?
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.
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.
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
@GPNaveen just showed me that I was thinking much too complicated! Kudos.
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 wrote:
@Kurt_Bremser, What do you mean by by-group processing? Are you referring to the executing the different values from a dataset?
I already gave you an answer to that question in https://communities.sas.com/t5/Base-SAS-Programming/How-to-create-a-list-of-macro-variables-using-pr...
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.
proc sql; select count(distinct cPOSMode) into :cPOSModeCNT separated by ' ' from have; select distinct cPOSMode into :cPOSMode1-:cPOSMode&cPOSModeCNT. from have; quit;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.