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 |
@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;
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;
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 |
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.