topic Re: How to use macro along with into:? in SAS Programming

How to use macro along with into:?

afiqcjohari — Thu, 16 Mar 2017 08:03:44 GMT

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?

Re: How to use macro along with into:?

gamotte — Thu, 16 Mar 2017 08:35:25 GMT

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.

Re: How to use macro along with into:?

Kurt_Bremser — Thu, 16 Mar 2017 08:42:58 GMT

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.

Re: How to use macro along with into:?

GPNaveen — Thu, 16 Mar 2017 08:43:24 GMT

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

Re: How to use macro along with into:?

Kurt_Bremser — Thu, 16 Mar 2017 08:45:08 GMT

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

Re: How to use macro along with into:?

Kurt_Bremser — Thu, 16 Mar 2017 08:48:13 GMT

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.

Re: How to use macro along with into:?

RW9 — Thu, 16 Mar 2017 09:20:48 GMT

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.

Re: How to use macro along with into:?

Ksharp — Thu, 16 Mar 2017 10:49:02 GMT

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

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

Re: How to use macro along with into:?

afiqcjohari — Fri, 17 Mar 2017 02:20:44 GMT

@Kurt_Bremser, What do you mean by by-group processing? Are you referring to the executing the different values from a dataset?

Re: How to use macro along with into:?

afiqcjohari — Fri, 17 Mar 2017 02:26:40 GMT

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.

Re: How to use macro along with into:?

afiqcjohari — Fri, 17 Mar 2017 02:29:10 GMT

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 🙂

Re: How to use macro along with into:?

Tom — Fri, 17 Mar 2017 03:27:12 GMT

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;

Re: How to use macro along with into:?

Kurt_Bremser — Fri, 17 Mar 2017 06:08:59 GMT

@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-proc-SQL/m-p/341449#M78153