How to compare values in 2 rows within a by group

[benjamin_2018]

Hello. I have a dataset of specifications for certain items, and each item has an old model and a new model. The data looks something like this. 

data items;
input Item $ SpecA $ SpecB $ SpecC $ ;
datalines;
ABC A3 B2 C5
ABC A3 B3 .
DEF .  B2 C4
DEF A1 .  C9
GHI A4 B9 C6
GHI A1 B9 .
;
run;

I want to, for each Item in the first column, compare SpecA, SpecB, and SpecC.

I want the output to have the following:

1) If there is a change from missing to not missing, or a change in value, then return the value of the 2nd row in the by group (the new model).

2) If there is a change from not missing to missing, then return the value from the 1st row in the by group (the old model).

3) If there is no change, then return a blank value. 

So here is what the output for the above would look like:

Item  SpecA  SpecB  SpecC

ABC  .           B3        C5

DEF  A1       B2         C9

GHI   A1      .             C6

Thank you for any suggestions on the best approach. I have tried using a array but I'm not too experienced with that feature. I typically use SAS Enterprise Guide 7.15 and SAS 9.4

[novinosrin · Accepted solution]

Hi @benjamin_2018  I'm curious to know why does the sample always have sets of 2 items. Is that really a good representative of your real?

Anyways, for what it's worth-->

data items;
input Item $ SpecA $ SpecB $ SpecC $ ;
datalines;
ABC A3 B2 C5
ABC A3 B3 .
DEF .  B2 C4
DEF A1 .  C9
GHI A4 B9 C6
GHI A1 B9 .
;
run;

data want;
 set items;
 by item;
 array s  SpecA  SpecB  SpecC;
 array l(3) $ _temporary_;
 do over s;
  if first.item then l(_i_)=s;
  else do;
   if l(_i_)>' ' and s=' ' then s=l(_i_);
   else if l(_i_)>' ' and s=l(_i_) then s=' ';
  end;
 end;
 if last.item;
run; 

[benjamin_2018]

@novinosrinThank you so much for the input. I will test out this code next week. This dataset has 2 items because I am examining a substitution from an old model to a new model and identifying the specifications that change between models. The items are grouped by similar type, for example, breads, cars, sewing machines, etc. 

[benjamin_2018]

I got this to work! Thank you. I just added a length to the elements in the temporary array because in my real dataset the lengths for all character variables are 64. 

[novinosrin]

Hi @benjamin_2018  I'm glad. Yes Indeed, I did assume you would be able to adjust the length. Good 🙂 Have a good day and stay safe

[mkeintz]

This would probably benefit from use a conditional SET with POINT=  and a bunch of renames:

data items;
input Item $ SpecA $ SpecB $ SpecC $ ;
datalines;
ABC A3 B2 C5
ABC A3 B3 .
DEF .  B2 C4
DEF A1 .  C9
GHI A4 B9 C6
GHI A1 B9 .
run;


data want (drop=frst_:);
  set items;
  by item;
  if first.item then set items (rename=(speca=frst_speca specb=frst_specb specc=frst_specc)) point=_n_; 
  array spc  spec:;
  array fsp  frst_spec: ;
  if last.item;
  do over spc ;
    if spc=fsp then spc=' '; else
    if missing(spc) then spc=fsp; 
  end;
run;

[benjamin_2018]

@mkeintzOk, thank you! I've not used the point= option on the set statement so I will need to look at that. At least I was on the right path by using 2 arrays, a first. and last. automatic variable with the by group, and a do loop. I will test this option out when I'm back at work next week. Thank you very much! Ben

[mkeintz]

You can also use a self-merge with offset as below - no extra SET statement needed:

data items;
input Item $ SpecA $ SpecB $ SpecC $ ;
datalines;
ABC A3 B2 C5
ABC A3 B3 .
DEF .  B2 C4
DEF A1 .  C9
GHI A4 B9 C6
GHI A1 B9 .
run;
data want (drop=frst_:);
  merge items (rename=(item=frst_item speca=frst_speca specb=frst_specb specc=frst_specc))
        items (firstobs=2);
  if item=frst_item;
  array spc  spec:;
  array fsp  frst_spec: ;
  do over spc ;
    if spc=fsp then spc=' '; else
    if missing(spc) then spc=fsp; 
  end;
run;

But note you can NOT use a BY statement with this merge.  Because if you do, the initial offset of the two streams of data from HAVE will be lost when the second BY-group is encountered.  I.e. the first output obs would have data from incoming obs1 and obs2, but the second output obs would pause the second data stream (as it had already reached the end of the by group.