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?

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?

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: 10,516

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: 6,946

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

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.

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

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