BookmarkSubscribeRSS Feed
TH2000
Calcite | Level 5

I have a data set similar to the example at the bottom.  The example represent two sets of variables.  Set #1 is advfnl1 - advfnl6 and set #2 is advfnl1_c - advfnl6_c.

My goal is to create 4 new variables and take the top 2 from the advfnl1 - advfnl6 set and the top 2 from the advfnl1_c - advfnl6_c set...But the top two from each set cannot be a duplicate in the final 4 values saved.

The hierachy is as follows:

1) Always take the first value from advfnl1 and place into "NEWVAR1".

2) The next value comes from advfnl1_c you would place that in 'NEWVAR2' unless it's the same value as 'NEWVAR1'.  If it would be the same value as 'NEWVAR1' then use the value that is in advnl2_c and place that value in "NEWVAR2".

3) Place the value of advfnl2 in "NEWVAR3" unless it's the same value as 'NEWVAR1' or 'NEWVAR2'.  If it is the same value then use advfnl3.

4) Place the value of advfnl2_c in "NEWVAR4" unless it's the same value as 'NEWVAR1' or 'NEWVAR2' or 'NEWVAR3'.  If it is the same value then ues advfnl3_c.

Using the example below (and my logic above) the final results would look like:

NEWVAR1NEWVAR2NEWVAR3NEWVAR4
34402949

IDadvfnl1advfnl2advfnl3advfnl4advfnl5advfnl6advfnl1_cadvfnl2_cadvfnl3_cadvfnl4_cadvfnl5_cadvfnl6_c
1234342943381254402934495422

Let me know if you can assist me in building this array.

Thanks.

2 REPLIES 2
data_null__
Jade | Level 19

Based on what I think you want this is what I came up with.

data id;
   length id $4;
  
array new[4];
   array advfnl[6];
   array c_advfnl[6];
   input ID advfnl
  • c_advfnl
  • ;
  •    j = 0;
      
    do i = 1 to dim(advfnl);
          y = whichn(advfnl,of new
  • );
         
  • if not y then do; j+1; new=advfnl; end;
         
    if j eq dim(new) then leave;

         
    do k = 1 to dim(c_advfnl);    
            
    x=whichn(c_advfnl,of new
  • );
  •          if not x then do; j+1; new=c_advfnl; leave; end;
            
    end;
         
    if j eq dim(new) then leave;
          end;     
      
    drop i j k x y;
       cards;
    1234 34 29 43 38 12 54   40 29 34 49 54 22
    1235 34 29 43 38 12 54   34 29 34 49 54 22
    1236 34 29 43 38 12 54   34 29 34 29 34 22
    1237 34 29 34 29 12 54   34 29 34 29 34 22
    1238 34 29 34 29 12 54   34 34 34 34 34 34
    1239 34 29 34 29 29 29   34 34 34 34 34 34
    ;;;;
       run;
    proc print heading=v;
       run;
    Fugue
    Quartz | Level 8

    Your text explanation does not match your expected results:

    You state newvar 4 should be 49.

    However, your explanation for newvar 4 is: "Place the value of advfnl2_c in "NEWVAR4" unless it's the same value as 'NEWVAR1' or 'NEWVAR2' or 'NEWVAR3'.  If it is the same value then ues advfnl3_c.".

    In your example, advfnl2_c = 29 and that matches newvar3. According to your instructions, we should, therefore, set newvar4 to advfnl3_c (=34).

    Either your expected results are wrong, or your explanation is wrong.

    Regardless, you could something like the following logic (which adheres to your explanation -- not your expected result). I've re-named your variables for convenience:

    data new ;

    merge old1 old2;

    by idno;

    format newvar1-newvar4 3.;

    newvar1 = advfnl_1;

    if advfnl_c1 = newvar1 then newvar2 = advfnl_c2;

    else newvar2 = advfnl_c1 ;

    if advfnl_2 = newvar1 or advfnl_2 = newvar2 then newvar3 = advfnl_3;

    else newvar3 = advfnl_2;

    if advfnl_c2 = newvar1 or advfnl_c2 = newvar2 or advfnl_c2 = newvar3 then newvar4 = advfnl_c3;

    else newvar4 = advfnl_c2;

    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
    • 2 replies
    • 398 views
    • 0 likes
    • 3 in conversation