topic Re: Creating a new variable where all rows with the same id display the first value for that id in SAS Programming

Creating a new variable where all rows with the same id display the first value for that id

ESpiel — Thu, 28 May 2020 23:23:42 GMT

My data contains many rows, with multiple rows for some ids. I would like to create a new date variable based on a date column I have, such that all rows with the same id will display the EARLIEST date for that id. I've attached an example of what I mean.

What would be the best way to accomplish this? Do I need to use PROC SQL and a join, or is there an easy way to do this in SAS?

Re: Creating a new variable where all rows with the same id display the first value for that id

novinosrin — Thu, 28 May 2020 23:54:08 GMT

data  want;
 set have;
 by id;
 retain need;
 if first.id then need=date;
run;

Re: Creating a new variable where all rows with the same id display the first value for that id

ESpiel — Fri, 29 May 2020 00:10:22 GMT

Sorry, I realized after trying your solution that there is one more element I forgot in the sample. I uploaded a new one.

My issue is it may not actually be the earliest date for that id in the whole dataset, but I need the earliest date for each id where a certain indicator=1.

This code doesn't work, but the idea would be something similar to:

data want;
  set have;
  by id;
  retain need;
  if first.id AND indicator=1 then need=date;
  else need=.;
run;

Re: Creating a new variable where all rows with the same id display the first value for that id

novinosrin — Fri, 29 May 2020 00:22:06 GMT

Hi @ESpiel Alright please try this-

data want;
  set have;
  by id;
  retain need;
  if first.id then need=.;
  if not need and indicator=1 then need=DATE_HAVE; 
  format need mmddyy10.; 
run;