BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EEEY
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

    View solution in original post

    9 REPLIES 9
    ballardw
    Super User

    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
    Obsidian | Level 7

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

    EEEY
    Obsidian | Level 7

    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
    Super User

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

    data_null__
    Jade | Level 19

    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;
    Capture.PNG
    EEEY
    Obsidian | Level 7

    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__
    Jade | Level 19

    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
    Obsidian | Level 7

    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__
    Jade | Level 19

    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;

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

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