SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How do I group unique dates for each subject id in SAS?

Reply
Learner
Posts: 1

How do I group unique dates for each subject id in SAS?

Hello, SAS Community!

I'm looking for some assistance in terms of manipulating data into a usable format. My eventual goal is to turn my data from long to wide format as it now has multiple rows of data per subject; however, that is not my question for today. I can describe the issue using the example data set below.

 

Each subject has multiple rows of data, I need code to create a new variable that will “group” the unique dates for each subject id (i.e., the WANTED variable).  So for example 1101 has 3 unique days of data, for each row I need the first date 3/12/12 to have a key variable indicating day 1.  For each row with the date 3/21/12, we need the key variable to indicate day 2 and for 3/25/12 the key variable would indicate day 3.  With the start of the next subject 1102, the first date 3/12/12 would indicate day 1…and so on. Does anyone have an idea of how I might make this "wanted variable" in SAS? Thank you so much!

  

 

Subjid

Date of Intake

Timept

Total Grams

WANTED Variable

1101

3/12/2012

0

12.67

1

1101

3/12/2012

0

37

1

1101

3/12/2012

0

152.16

1

1101

3/12/2012

0

96

1

1101

3/21/2012

0

215

2

1101

3/21/2012

0

217.5

2

1101

3/21/2012

0

309

2

1101

3/25/2012

0

98

3

1101

3/25/2012

0

262.01

3

1102

3/12/2012

0

155

1

1102

3/12/2012

0

64

1

1102

3/22/2012

0

262

2

1102

3/22/2012

0

39.8

2

1102

3/22/2012

0

159.75

2

1102

3/22/2012

0

474

2

1102

3/25/2012

0

184.28

3

1102

3/25/2012

0

502.35

3

1105

3/27/2012

0

158.5

1

1105

3/27/2012

0

40.5

1

1105

3/27/2012

0

23.2

1

1105

4/3/2012

0

55

2

 

 

 

Respected Advisor
Posts: 2,054

Re: How do I group unique dates for each subject id in SAS?

Posted in reply to CCHMC_research

 

proc sort data=have;
    by subjid date;
run;
data want;
    set have;
    by subjid date;
    if first.subjid then wantedvariable=0;
    if first.date then wantedvariable+1;
run;

 

 

--
Paige Miller
Ask a Question
Discussion stats
  • 1 reply
  • 186 views
  • 0 likes
  • 2 in conversation