DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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

Patient_IDDayLipid_LevelsFirst_Obs
00010

10.2

10.2
0001111.610.2
0001214.510.2
0001310.510.2
000209.99.9
0002110.79.9
0002211.49.9
0002311.19.9
0003014.314.3
0003117.214.3
0003214.114.3
0003314.514.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.


Accepted Solutions
Solution
‎04-29-2014 12:04 AM
Contributor SKK
Contributor
Posts: 35

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

Posted in reply to LukeKlein

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;

View solution in original post


All Replies
Solution
‎04-29-2014 12:04 AM
Contributor SKK
Contributor
Posts: 35

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

Posted in reply to LukeKlein

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;

Super User
Posts: 11,343

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

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

Contributor SKK
Contributor
Posts: 35

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

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;

New Contributor
Posts: 2

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

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;

Super User
Posts: 7,764

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

Posted in reply to LukeKlein

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 274 views
  • 0 likes
  • 4 in conversation