topic Re: Creating group by date in SAS Programming

Creating group by date

Moraes86 — Tue, 31 Aug 2021 16:57:43 GMT

Hi all,

I would like to create 3 groups by date: 1, 2 and 3, by group.

I also would like to know the interval days by each day.

So my main goal is: to create 3 groups by date( and my question is how to create the group "2", because in second group could have more then one day.

My other goal is to create the interval day for all days.

Observation: Is it possible to select the two earliest days?

Thanks all,

Daniel

My code is :

proc sql want as;

select id;

min(date), as first_date, max(date) as last_date

from have

group by id;

quit;

Have		Want
Id	Date	Id	Date	Group
A	8-Aug-19	A	8-Aug-19	1	(first date)
A	8-Aug-19	A	8/19/2019- 29 Aug	2
A	8-Aug-19	A	3-Sep-19	3	(last date)
A	19-Aug-19
A	19-Aug-19
A	19-Aug-19
A	29-Aug-19
A	29-Aug-19
A	29-Aug-19
A	29-Aug-19
A	29-Aug-19
A	3-Sep-19
A	3-Sep-19
A	3-Sep-19
A	3-Sep-19

Re: Creating group by date

PaigeMiller — Tue, 31 Aug 2021 17:03:10 GMT

Find the minimum date in HAVE, that is group 1. Find the maximum date in HAVE, that is group 3. Everything else is Group 2.

proc summary data=have nway;
    class id;
    var date;
    output out=minmax min=mindate max=maxdate;
run;

data want;
     merge have minmax;
     by id;
     if date=mindate then group=1;
     else if date=maxdate then group=3;
     else group=2;
run;

Re: Creating group by date

Reeza — Tue, 31 Aug 2021 17:03:31 GMT

Anything is possible, it's just how much work but in this case, these are very possible.

What are you rules that you want implemented - first two days go to group 1, last day to group 3 and everything else to group 2? Or just the rule that you've hardcoded?
Are the rules generic or specific to an ID? Will you have multiple IDs that need the same rule?

Re: Creating group by date

Moraes86 — Tue, 31 Aug 2021 17:48:49 GMT

Hi Reeza,

So, first two days go to group 1, last day to group 3 and everything else to group 2.
And I need the same rule for multiple IDs.

Re: Creating group by date

Reeza — Tue, 31 Aug 2021 18:19:35 GMT

Are the rules defined per ID or for the whole data set? Can you please update your sample data set to include more than one ID and to reflect the answer to this question? Please include expected output as well to ensure we can verify the answer.

Re: Creating group by date

Moraes86 — Tue, 31 Aug 2021 21:04:36 GMT

I would like to organize the data per ID.

I am sending more examples.
So, one thing that I need is:
- for the first and last date: to add/classify with 'first"(group 1) or "last"(group 30 ALSO the day with ONE day of difference. For example, in this data, it can be observed for Id B, the last date is 30 August, but I also would like to classify it as late the day 29 August.

	F0920419
Have		Want
Id	Date	Id	Date	Group
A	8-Aug-19	A	8-Aug-19	1
A	8-Aug-19	A	8-Aug-19	1
A	8-Aug-19	A	8-Aug-19	1
A	19-Aug-19	A	19-Aug-19	2
A	19-Aug-19	A	19-Aug-19	2
A	19-Aug-19	A	19-Aug-19	2
A	29-Aug-19	A	29-Aug-19	2
A	29-Aug-19	A	29-Aug-19	2
A	29-Aug-19	A	29-Aug-19	2
A	29-Aug-19	A	29-Aug-19	2
A	29-Aug-19	A	29-Aug-19	2
A	3-Sep-19	A	3-Sep-19	3
A	3-Sep-19	A	3-Sep-19	3
A	3-Sep-19	A	3-Sep-19	3
A	3-Sep-19	A	3-Sep-19	3
B	1-Aug-19	B	1-Aug-19	1
B	1-Aug-19	B	1-Aug-19	1
B	1-Aug-19	B	1-Aug-19	1
B	9-Aug-19	B	9-Aug-19	2
B	9-Aug-19	B	9-Aug-19	2
B	9-Aug-19	B	9-Aug-19	2
B	9-Aug-19	B	9-Aug-19	2
B	12-Aug-19	B	12-Aug-19	2
B	13-Aug-19	B	13-Aug-19	2
B	14-Aug-19	B	14-Aug-19	2
B	15-Aug-19	B	15-Aug-19	2
B	16-Aug-19	B	16-Aug-19	2
B	17-Aug-19	B	17-Aug-19	2
B	17-Aug-19	B	17-Aug-19	2
B	17-Aug-19	B	17-Aug-19	2
B	19-Aug-19	B	19-Aug-19	2
B	20-Aug-19	B	20-Aug-19	2
B	21-Aug-19	B	21-Aug-19	2
B	22-Aug-19	B	22-Aug-19	2
B	22-Aug-19	B	22-Aug-19	2
B	23-Aug-19	B	23-Aug-19	2
B	26-Aug-19	B	26-Aug-19	2
B	27-Aug-19	B	27-Aug-19	2
B	29-Aug-19	B	29-Aug-19	3
B	30-Aug-19	B	30-Aug-19	3
B	30-Aug-19	B	30-Aug-19	3

Re: Creating group by date

PaigeMiller — Tue, 31 Aug 2021 21:10:36 GMT

@Moraes86 wrote:

I would like to organize the data per ID.

I am sending more examples.
So, one thing that I need is:
- for the first and last date: to add/classify with 'first"(group 1) or "last"(group 30 ALSO the day with ONE day of difference. For example, in this data, it can be observed for Id B, the last date is 30 August, but I also would like to classify it as late the day 29 August.

F0920419

Have Want

Id Date Id Date Group

A 8-Aug-19 A 8-Aug-19 1

A 8-Aug-19 A 8-Aug-19 1

A 8-Aug-19 A 8-Aug-19 1

A 19-Aug-19 A 19-Aug-19 2

A 19-Aug-19 A 19-Aug-19 2

A 19-Aug-19 A 19-Aug-19 2

A 29-Aug-19 A 29-Aug-19 2

A 29-Aug-19 A 29-Aug-19 2

A 29-Aug-19 A 29-Aug-19 2

A 29-Aug-19 A 29-Aug-19 2

A 29-Aug-19 A 29-Aug-19 2

A 3-Sep-19 A 3-Sep-19 3

A 3-Sep-19 A 3-Sep-19 3

A 3-Sep-19 A 3-Sep-19 3

A 3-Sep-19 A 3-Sep-19 3

B 1-Aug-19 B 1-Aug-19 1

B 1-Aug-19 B 1-Aug-19 1

B 1-Aug-19 B 1-Aug-19 1

B 9-Aug-19 B 9-Aug-19 2

B 9-Aug-19 B 9-Aug-19 2

B 9-Aug-19 B 9-Aug-19 2

B 9-Aug-19 B 9-Aug-19 2

B 12-Aug-19 B 12-Aug-19 2

B 13-Aug-19 B 13-Aug-19 2

B 14-Aug-19 B 14-Aug-19 2

B 15-Aug-19 B 15-Aug-19 2

B 16-Aug-19 B 16-Aug-19 2

B 17-Aug-19 B 17-Aug-19 2

B 17-Aug-19 B 17-Aug-19 2

B 17-Aug-19 B 17-Aug-19 2

B 19-Aug-19 B 19-Aug-19 2

B 20-Aug-19 B 20-Aug-19 2

B 21-Aug-19 B 21-Aug-19 2

B 22-Aug-19 B 22-Aug-19 2

B 22-Aug-19 B 22-Aug-19 2

B 23-Aug-19 B 23-Aug-19 2

B 26-Aug-19 B 26-Aug-19 2

B 27-Aug-19 B 27-Aug-19 2

B 29-Aug-19 B 29-Aug-19 3

B 30-Aug-19 B 30-Aug-19 3

B 30-Aug-19 B 30-Aug-19 3

The code I provided above does this, with one minor modification:

else if date=maxdate or date=maxdate-1 then group=3;

Re: Creating group by date

Moraes86 — Thu, 02 Sep 2021 19:57:44 GMT

Thank you very much. This code worked.