using Macro inside the coalescec() function

[EEEY]

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.

[ballardw]

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.

[EEEY]

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

[EEEY]

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!

[ballardw]

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

[data_null__ · Accepted solution]

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;

[EEEY]

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;

[data_null__]

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.

[EEEY]

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!

[data_null__]

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;