topic Re: Retain by two variables in SAS Programming

Retain by two variables

awardell — Fri, 19 Aug 2022 01:40:51 GMT

Hello,
I have a data set that looks like the following:

ID	DATE	VAR1	VAR2	VAR3
1	8-22-2022	a
1	8-22-2022		b
1	7-23-2021			c
2	11-12-2019	a
2	10-14-2019			c


data have;
input ID DATE :mmddyy10. VAR1 $ VAR2 $ VAR3 $ ;
format DATE mmddyy10.;
datalines;
1 08-22-2022 a . .
1 08-22-2022 . b .
1 07-23-2021 . . c
2 11-12-2019 a . .
2 10-14-2019 . . c
;
run;

I want something that looks like the following:

ID	DATE	VAR1	VAR2	VAR3
1	8-22-2022	a	b
1	8-22-2022	a	b
1	7-23-2021			c
2	11-12-2019	a
2	10-14-2019			c

data want;
input ID DATE :mmddyy10. VAR1 $ VAR2 $ VAR3 $ ;
format DATE mmddyy10.;
datalines;
1 08-22-2022 a b .
1 08-22-2022 a b .
1 07-23-2021 . . c
2 11-12-2019 a . .
2 10-14-2019 . . c
;
run;

Essentially, I need a data set where the values for var1 - var3 are retained if the id AND the date are the same. I tried something like this; however, it did not work (also other similar iterations of the code below (with first.id and first.date).


data test;
set have;
by id date;
retain met_ihc2ln_;
run;

Thank you in advance for any help you can provide!

Re: Retain by two variables

ballardw — Thu, 18 Aug 2022 21:41:16 GMT

Since your date is missing on the first example record how does that meet the requirement "the id AND the date are the same"? So you need to describe what is going on when a date obviously doesn't match that it should be processed this way.

Retain won't work directly because it can keep a value from a PREVIOUS record, not look ahead to the NEXT record as you are doing in a couple of places.

Re: Retain by two variables

awardell — Fri, 19 Aug 2022 01:41:44 GMT

I see what you are saying. I had made a mistake with the date being missing in my first chart, no dates should be missing. Do you know of a way that I could look to the NEXT record across date and id? I am still stuck with how to get this.

Re: Retain by two variables

SASKiwi — Fri, 19 Aug 2022 02:17:26 GMT

There are probably more elegant ways of doing this but SQL works for the data you've supplied:

proc sql;
  create table want as

  select A.ID
        ,A.DATE
        ,coalesce(A.VAR1, B.Max_VAR1) as VAR1 
        ,coalesce(A.VAR2, B.Max_VAR2) as VAR2
        ,coalesce(A.VAR3, B.Max_VAR3) as VAR3
  from have as A

  left join
  (select ID
          ,DATE
          ,max(VAR1) as Max_VAR1
          ,max(VAR2) as Max_VAR2
          ,max(VAR3) as Max_VAR3
   from have
   group by ID
           ,DATE
  ) as B
  on A.ID = B.ID
  and A.DATE = B.DATE
  order by A.ID
          ,A.DATE 
  ;
quit;

Re: Retain by two variables

andreas_lds — Fri, 19 Aug 2022 05:10:12 GMT

Just another solution:

data want;
   length v1-v3 $ 1;
   
   do _n_ = 1 by 1 until(last.date);
      set have;
      by id date notsorted;
      v1 = coalescec(v1, var1);
      v2 = coalescec(v2, var2);
      v3 = coalescec(v3, var3);
   end;
   
   do _n_ = 1 by 1 until(last.date);
      set have;
      by id date notsorted;
      var1 = coalescec(v1, var1);
      var2 = coalescec(v2, var2);
      var3 = coalescec(v3, var3);
   end;
   
   drop v1-v3;
run;

Re: Retain by two variables

Ksharp — Fri, 19 Aug 2022 12:41:38 GMT

data have;
input ID DATE :mmddyy10. VAR1 $ VAR2 $ VAR3 $ ;
format DATE mmddyy10.;
datalines;
1 08-22-2022 a . .
1 08-22-2022 . b .
1 07-23-2021 . . c
2 11-12-2019 a . .
2 10-14-2019 . . c
;
run;


proc sql;
create table want(drop=dummy) as
select ID,DATE,var1 as dummy,max(var1) as var1,max(var2) as var2,max(var3) as var3
 from have 
  group by ID,DATE ;
quit;

Re: Retain by two variables

Tom — Fri, 19 Aug 2022 13:38:20 GMT

Your example data is not complex enough to actually test all possible scenarios. In particular, what do you want to do when there is more than one non-missing value for a variable within a group?

Here is method using the UPDATE statement. First collapse to final values per group by using the existing data as transactions against an empty dataset. Then reapply the transactions to get the detailed observations written out.

data have ;
  input ID DATE :mmddyy. (VAR1-VAR3) (:$8.);
  format date yymmdd10.;
cards;
1 8-22-2022 a . .    
1 8-22-2022 . b . 
1 7-23-2021 . . c
2 11-12-2019 a . .  
2 10-14-2019 . . c
;

data step1;
  update have(obs=0) have ;
  by id descending date;
run;

data want ;
  update step1 have;
  by id descending date;
  output;
run;

Result:

Obs    ID          DATE    VAR1    VAR2    VAR3

 1      1    2022-08-22     a       b
 2      1    2022-08-22     a       b
 3      1    2021-07-23                     c
 4      2    2019-11-12     a
 5      2    2019-10-14                     c

Re: Retain by two variables

awardell — Fri, 19 Aug 2022 18:17:32 GMT

Thank you so much! This worked great! I appreciate all of your help!

Re: Retain by two variables

awardell — Fri, 19 Aug 2022 18:18:34 GMT

Thank you so much for your help! I have never seen the coalesce function used like this, and I appreciate it!

Re: Retain by two variables

awardell — Fri, 19 Aug 2022 18:21:09 GMT

Thank you so much for this solution! I appreciate your help!