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

CCHMC_research — Thu, 28 Sep 2017 18:06:06 GMT

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

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

PaigeMiller — Thu, 28 Sep 2017 18:15:10 GMT

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;

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

TeresaZ — Fri, 08 Nov 2019 16:19:48 GMT

Thank you. This solved my problem too.

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

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

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

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