Creating a new variable based on two others

mitrakos — Wed, 21 Jul 2021 18:11:27 GMT

Hi Everyone,

I have some data, below is a test sample of what I have .

data have;
input ID $ date:mmddyy10. value;
format date mmddyy10.;
datalines;
1 12/01/2020 5
1 12/02/2020 6
1 12/03/2020 7
2 12/01/2020 10
2 12/02/2020 8
2 12/03/2020 9
3 12/01/2020 2
3 12/02/2020 2
3 12/03/2020 2
4 12/01/2020 5
4 12/02/2020 8
4 12/03/2020 6 
5 12/01/2020 2
5 12/02/2020 5
5 12/03/2020 4 
;
run;

Essentially what I want to do is per ID number, I want to create a new variable called day and have it number from the first date to the last date as day 1, 2, 3, etc. Below is an example of what I would want to do.

data have;
input ID $ date:mmddyy10. value day;
format date mmddyy10.;
datalines;
1 12/01/2020 5 1
1 12/02/2020 6 2
1 12/03/2020 7 3
2 12/01/2020 10 1 
2 12/02/2020 8 2
2 12/03/2020 9 3 
3 12/01/2020 2 1 
3 12/02/2020 2 2
3 12/03/2020 2 3
4 12/01/2020 5 1 
4 12/02/2020 8 2
4 12/03/2020 6 3
5 12/01/2020 2 1
5 12/02/2020 5 2
5 12/03/2020 4 3
;
run;

I tried to originally create a do loop with first.date and last.date but I wasn't sure how to incorporate the ID number as well, and when I tried to I kept getting errors 😅. Any ideas on how to create this? Thank you!

Re: Creating a new variable based on two others

PGStats — Wed, 21 Jul 2021 18:36:05 GMT

Use proc rank:

proc rank data=have out=want;
by id;
var date;
ranks day;
run;

Re: Creating a new variable based on two others

mitrakos — Wed, 21 Jul 2021 19:05:51 GMT

Turns out I was making a simple problem into a very complicated solution. Thank you!!

topic Re: Creating a new variable based on two others in SAS Programming

Creating a new variable based on two others

Re: Creating a new variable based on two others

Re: Creating a new variable based on two others