topic Re: using Macro inside the coalescec() function in SAS Programming

using Macro inside the coalescec() function

EEEY — Fri, 21 Aug 2015 17:06:20 GMT

I need to find the first character values from a list of varialbes. Unfortunately the number of varialbes vary month to month.

So I wrote a Macro to define this list of variables based on the tot # of variables for the month. The program I used as follows:

%macro id_list(count=);

%do i=&count %to 2 ;

mgrid_&count,

%end;

%mend;

data test2;

set test;

last_mgrid=coalescec(%id_list(count=10) mgrid_1);

run;

I am assuming there are 10 variables (mgrid_1 -- mgrid_10). For Example, one record has values like:

mgrid_1 mgrid_2 mgrid_3 mgrid_4 mgrid_5 mgrid_6....mgrid_10

111 222 333

The result I am looking for is:

last_mgrid=333

But I always got last_mgrid=111. The Macro %id_list did not work inside the coalescec() function.

Please see if anyone has any experience on this.

Thank you very much for your thought.

Re: using Macro inside the coalescec() function

ballardw — Fri, 21 Aug 2015 17:21:02 GMT

You'll likely have better luck with:

%do i=&count %to 2 %by -1;

AND

mgrid_&I,

your existing code when count was greater than or equal to 2 sees 10>2 and does not execute the loop at all.

And would only generate values ending in COUNT.

Re: using Macro inside the coalescec() function

data_null__ — Fri, 21 Aug 2015 17:42:55 GMT

You don't need a macro you just need a reversed enumerated list where the last variable is unknown. e.g. mgrid10-mgrid1 where we look-up the name MGRID10

Assuming that your enumerated variables are always in the proper order 1-n this will work. If you can't depend on the order being right it will take a bit more work.

data mgrid;
   array mgrid[13]; /*this will change we need to know the name of the last variable in the list */
   retain mgrid1 1 mgrid2 2 mgrid3 333; 
   run; 

/*find that name*/
data _null_; 
   if 0 then set mgrid(keep=mgrid:);
   array m mgrid:;
   call symputx('MAXMGRID',vname(m[dim(m)]));
   stop; *don't forget this;
   run; 
%put &=maxmgrid;

/*now find the last non missing*/
data mgrid2;
   set mgrid;
   last_mgrid = coalesce(of &maxmgrid-mgrid1);
   run; 
proc print; 
   run; 

Re: using Macro inside the coalescec() function

EEEY — Fri, 21 Aug 2015 18:42:54 GMT

Thanks for your quick response. I have tried to add %by -1 but still gave me the value of mgrid_1..:-(

Re: using Macro inside the coalescec() function

EEEY — Fri, 21 Aug 2015 18:48:33 GMT

Although I am not very familiar with Array (try to avoid to use Array in my programming), the code you provided worked really well. Really appreciated your help. I have modified the program to the following and got exactly I was looking for.

/*find that name*/

data _null_;

set saslib.mgr_10(keep=mgrid_:);

if 0 then set mgrid(keep=mgrid_:);

array m

mgrid_: _character_;

call symputx('MAXMGRID',vname(m[dim(m)]));

stop; *don't forget this;

run;

%put &=maxmgrid;

/*now find the last non missing*/

data test;

set saslib.mgr_10;

format last_mgrid z8.;

last_mgrid = coalesce(of &maxmgrid-mgrid_1);

run;

Re: using Macro inside the coalescec() function

data_null__ — Fri, 21 Aug 2015 18:54:00 GMT

You need to change these two lines

set saslib.mgr_10(keep=mgrid_:);

if 0 then set mgrid(keep=mgrid_:);

to this

if 0 then set saslib.mgr_10(keep=mgrid_:);

You don't need to set even one obs to determine the name of the last MGRID_nnn variable.

Re: using Macro inside the coalescec() function

EEEY — Fri, 21 Aug 2015 19:20:11 GMT

Sorry Ballardw - your comments works correctly too.. I missed the second part of your comments by changing to mgrid_&i instead of mgrid_&count..Thank you!

Re: using Macro inside the coalescec() function

EEEY — Fri, 21 Aug 2015 19:34:54 GMT

Thanks data_null_.. You were right. I should remove that..

I just run into another issue by using that code.

some of the mgrid_: values include the leading zero, some do not. Then new variable last_mgrid is supposed to keep original format. For example, Since some mgrid is like 000111, some like 222. By using the code above, the leading zeros got eliminated to 111 or if I defined last_mgrid as z8., the value of 222 became 00000222. I don't want to see this. Just want to get the original value and their format.. How should I do? Thanks!

Re: using Macro inside the coalescec() function

ballardw — Fri, 21 Aug 2015 19:35:37 GMT

I didn't catch it at first either and had to go back and add it.

Re: using Macro inside the coalescec() function

data_null__ — Fri, 21 Aug 2015 21:00:41 GMT

I think you are saying that each MGRIDn variable may have a different numeric format associated with it and you want to create LAST_MGRID and keep that formatted value (into a character variable). That's what this code does. If that's not what you are saying say it again.

data mgrid;
   infile cards missover; 
   array mgrid[13]; /*this will change we need to know the name of the last variable in the list */
   input mgrid;
   format mgrid3 z6. mgrid5 3. mgrid2 8.3; 
   cards; 
1 4 333
1 2 3 444 555
1 2 
;;;;
   run; 
proc print; 
   run; 

/*find that name*/
data _null_; 
   if 0 then set mgrid(keep=mgrid:);
   array m mgrid:;
   call symputx('MAXMGRID',vname(m[dim(m)]));
   stop; *don't forget this;
   run; 
%put &=maxmgrid;

/*now find the last non missing*/
data mgrid2;
   set mgrid;
   array _m &maxmgrid-mgrid1;
   if 0 then set mgrid(keep=mgrid1 rename=(mgrid1=last_mgrid));
   last_mgrid = coalesce(of _m);
   w = whichn(last_mgrid,of _m
);
   
length last_mgrid_formatted $32; 
   last_mgrid_formatted = vvalue(_m);
   run; 
proc print; 
   run;