How to assign value to ew variable from existing variable within by group

[r3570]

Hi All,

I have data as below:

USUBJID $ AMT EXSTDTC $;
101 100 12JUN2023
101 500 13JUN2023
101 100 11JUN2023
102 100 12JUN2023
102 500 13JUN2023
102 100 11JUN2023
102 100 12JUN2023
102 500 13JUN2023
103 100 11JUN2023

I need to sort the data based on dates within same usubjid group and take the last amt value from each group and assign the value to new variable. My output should be as below

USUBJID $ AMT EXSTDTC $  NEW_VAR
101 100 11JUN2023  500
101 100 12JUN2023 500
101 500 13JUN2023 500
102 100 11JUN2023  500
102 100 12JUN2023  500
102 500 13JUN2023  500
103 100 11JUN2023 500
103 100 12JUN2023 500
103 500 13JUN2023 500

Please help

[112211]

This code works for u;

data sort_date;
input USUBJID $ AMT EXSTDTC $;
cards;
101 100 12JUN2023
101 500 13JUN2023
101 100 11JUN2023
102 100 12JUN2023
102 500 13JUN2023
102 100 11JUN2023
103 100 12JUN2023
103 500 13JUN2023
103 100 11JUN2023
;
data want;
set sort_date;
new_var = 500;
run;
proc sort data = want;
by USUBJID exstdtc ;

run;

[r3570]

It's not about 500 being the constant value over there by directly assigning 500 as the value. It's all about the last dose per usubjid which should be populated as a new variable for each group of usubjid. The code which you provided is direct hard coding. What if there is some other number apart from 500

[Tom]

So just find the last dose. Note to properly SORT the data you will need an actual DATE variable and not the character variable EXSTDTC.

data have;
  input USUBJID $ AMT date :date.;
  format date date9.;
  EXSTDTC=put(date,date9.);
cards;
101 100 12JUN2023
101 500 13JUN2023
101 100 11JUN2023
102 100 12JUN2023
102 500 13JUN2023
102 100 11JUN2023
103 100 12JUN2023
103 500 13JUN2023
103 100 11JUN2023
;

proc sort data=have out=sort_date;
  by usubjid date ;
run;

data lastdose;
  set sort_date;
  by usubjid;
  where not missing(amt);
  if last.usubjid;
  keep usubjid amt;
  rename amt=lastdose;
run;

Then just merge it back with original data.

data want;
  merge sort_date lastdose;
  by usubjid;
run;

results

Obs    USUBJID    AMT         date     EXSTDTC     lastdose

 1       101      100    11JUN2023    11JUN2023       500
 2       101      100    12JUN2023    12JUN2023       500
 3       101      500    13JUN2023    13JUN2023       500
 4       102      100    11JUN2023    11JUN2023       500
 5       102      100    12JUN2023    12JUN2023       500
 6       102      500    13JUN2023    13JUN2023       500
 7       103      100    11JUN2023    11JUN2023       500
 8       103      100    12JUN2023    12JUN2023       500
 9       103      500    13JUN2023    13JUN2023       500

[ballardw]

Suggestion for future questions: Provide example/solutions that get different solution values for the by groups or other conditions. When every "group" has the same required output then it starts looking the input data doesn't really matter.

[Kurt_Bremser]

Before you do anything else, convert those useless date strings to SAS date values by using the INPUT function with a DATE9. informat.

Once that is done:

proc sort date=have;
by usubjid exstdtc;
run;

data want;
do until (last.usubjid);
  set have;
  by usubjid;
end;
new_var = amt;
do until (last.usubjid);
  set have;
  by usubjid;
  output;
end;
run;