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.
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;
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;
Probably don't want the First.day as it will reset for each unique value of day.
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.