DATA Step, Macro, Functions and more

How to use macro along with into:?

Reply
Frequent Contributor
Posts: 99

How to use macro along with into:?

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?

Regular Contributor
Posts: 194

Re: How to use macro along with into:?

[ Edited ]

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.

Super User
Posts: 6,948

Re: How to use macro along with into:?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: How to use macro along with into:?

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 

Super User
Posts: 6,948

Re: How to use macro along with into:?

@GPNaveen just showed me that I was thinking much too complicated! Kudos.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 99

Re: How to use macro along with into:?

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 Smiley Happy
Super User
Posts: 6,948

Re: How to use macro along with into:?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 99

Re: How to use macro along with into:?

@KurtBremser, What do you mean by by-group processing? Are you referring to the executing the different values from a dataset?
Super User
Posts: 6,948

Re: How to use macro along with into:?


afiqcjohari wrote:
@KurtBremser, 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...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,408

Re: How to use macro along with into:?

I have to agree with @KurtBremser 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.

Frequent Contributor
Posts: 99

Re: How to use macro along with into:?

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.
Super User
Posts: 9,682

Re: How to use macro along with into:?

proc sql;
	select count(distinct cPOSMode) into :cPOSModeCNT  separated by ' '
	from have;

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

Super User
Super User
Posts: 6,502

Re: How to use macro along with into:?

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;
Ask a Question
Discussion stats
  • 12 replies
  • 227 views
  • 4 likes
  • 7 in conversation