How can I create a new_variable that is a constant within a subgroup?

[LukeKlein]

I have the following issue in SAS:  Consider the table below:

Patient_ID	Day	Lipid_Levels	First_Obs
0001	0	10.2	10.2
0001	1	11.6	10.2
0001	2	14.5	10.2
0001	3	10.5	10.2
0002	0	9.9	9.9
0002	1	10.7	9.9
0002	2	11.4	9.9
0002	3	11.1	9.9
0003	0	14.3	14.3
0003	1	17.2	14.3
0003	2	14.1	14.3
0003	3	14.5	14.3

I have a dataset named "indata" with the variable Patient_ID, Day, and Lipid_Level, and I need to create a new variable First_Obs that is equal to the first observation for a given patient ( i.e. the Lipid Level when the Day-within-Patient_ID value is equal to zero).

Again, I have the first three columns, and I need to create the fourth.  Any suggestions??

Thank you for your help.

[SKK · Accepted solution]

Hi there are several methods to do BOCF...Try this..

PROC SORT DATA=indata;
BY PATIENT_ID DAY;
RUN;

DATA WANT;
SET indata;
RETAIN FIRST_OBS;
BY PATIENT_ID DAY;
IF FIRST.PATIENT_ID AND FIRST.DAY THEN FIRST_OBS=LIPID_LEVELS;
RUN;

[ballardw]

Probably don't want the First.day as it will reset for each unique value of day.

[SKK]

Try this:

PROC SQL;

CREATE TABLE WANT AS

SELECT A.PATIENT_ID, DAY , LIPID, FIRST_OBS  FROM HAVE AS A INNER JOIN

(SELECT PATIENT_ID, LIPID AS FIRST_OBS FROM HAVE GROUP BY PATIENT_ID HAVING DAY=MIN(DAY)) AS B

ON A.PATIENT_ID=B.PATIENT_ID;

QUIT;

[LukeKlein]

Thank you very much! 

I used the following code to achieve what I needed, then found your response:

proc sort data = indata;

by Pateint_ID day

run;

data want;

  set indata;

  by Patient_ID;

  retain First_Obs;

  if first.Patient_ID then FirstObs=Lipid_Levels;

run;quit;

[Kurt_Bremser]

Does the patient ID change if the same person is admitted repeatedly?

If not, you may need to include the actual date in the data set.

Then try the following:

proc sort data=HAVE;

by PATIENT_ID DATE;

run;

data WANT;

set HAVE;

retain FIRST_OBS;

if DAY = 0 then FIRST_OBS=LIPID_LEVELS;

run;

If, instead, patients get a new PATIENT_ID on each admission, change DATE in the sort to DAY;