Creating group by date

[Moraes86]

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

[PaigeMiller]

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;

[Reeza]

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?

[Moraes86]

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.

[Reeza]

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.

[Moraes86]

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

[PaigeMiller · Accepted solution]

---

@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;

[Moraes86]

Thank you very much. This code worked.