DATA Step, Macro, Functions and more

using Macro inside the coalescec() function

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

using Macro inside the coalescec() function

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.


Accepted Solutions
Solution
‎08-21-2015 01:42 PM
Respected Advisor
Posts: 3,799

Re: using Macro inside the coalescec() function

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=mgridSmiley Happy;
   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;
    Capture.PNG

    View solution in original post


    All Replies
    Super User
    Posts: 11,343

    Re: using Macro inside the coalescec() function

    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.

    Contributor
    Posts: 26

    Re: using Macro inside the coalescec() function

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

    Contributor
    Posts: 26

    Re: using Macro inside the coalescec() function

    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!

    Super User
    Posts: 11,343

    Re: using Macro inside the coalescec() function

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

    Solution
    ‎08-21-2015 01:42 PM
    Respected Advisor
    Posts: 3,799

    Re: using Macro inside the coalescec() function

    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=mgridSmiley Happy;
       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;
    Capture.PNG
    Contributor
    Posts: 26

    Re: using Macro inside the coalescec() function

    Posted in reply to data_null__

    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_Smiley Happy;

       if 0 then set mgrid(keep=mgrid_Smiley Happy;

       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;

    Respected Advisor
    Posts: 3,799

    Re: using Macro inside the coalescec() function

    You need to change these two lines

    set saslib.mgr_10(keep=mgrid_Smiley Happy;

       if 0 then set mgrid(keep=mgrid_Smiley Happy;

    to this

       if 0 then set saslib.mgr_10(keep=mgrid_Smiley Happy;

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

    Contributor
    Posts: 26

    Re: using Macro inside the coalescec() function

    Posted in reply to data_null__

    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!

    Respected Advisor
    Posts: 3,799

    Re: using Macro inside the coalescec() function

    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=mgridSmiley Happy;
       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;
    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 9 replies
    • 346 views
    • 5 likes
    • 3 in conversation