topic Re: How can I create a new_variable that is a constant within a subgroup? in SAS Programming

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

LukeKlein — Tue, 29 Apr 2014 02:03:04 GMT

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.

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

SKK — Tue, 29 Apr 2014 04:04:47 GMT

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;

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

ballardw — Tue, 29 Apr 2014 15:52:27 GMT

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

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

SKK — Wed, 30 Apr 2014 04:14:41 GMT

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;

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

Kurt_Bremser — Wed, 30 Apr 2014 06:47:51 GMT

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;

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

LukeKlein — Thu, 01 May 2014 01:20:34 GMT

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;