Have
ID | Order | Age |
1 | 2 | 71 |
1 | 2 | 71 |
1 | 3 | 77 |
1 | 3 | 77 |
2 | 4 | 30 |
2 | 4 | 30 |
2 | 7 | 37 |
2 | 7 | 37 |
Want
ID | Order | Age | Visit |
1 | 2 | 71 | 1 |
1 | 2 | 71 | 1 |
1 | 3 | 77 | 2 |
1 | 3 | 77 | 2 |
2 | 4 | 30 | 1 |
2 | 4 | 30 | 1 |
2 | 7 | 37 | 2 |
2 | 7 | 37 | 2 |
Code and output from my attempt
proc sort have; by id order; run;
data want;
set have;
by id order;
if first.id then visit = 1;
if last.id then visit = 2;
run;
ID | Order | Age | Visit |
1 | 2 | 71 | 1 |
1 | 2 | 71 | . |
1 | 3 | 77 | . |
1 | 3 | 77 | 2 |
2 | 4 | 30 | 1 |
2 | 4 | 30 | . |
2 | 7 | 37 | . |
2 | 7 | 37 | 2 |
Obviously this only does the very first and last row, not sure how to fill in the others. Within each ID, the smaller order number should all be visit 1 and the larger should be visit 2. Order is random, but is always smaller for visit 1 and the same for both rows of a given visit.
Close 🙂
proc sort have; by id order; run;
data want;
set have;
by id order;
*set to 0 for first ID;
if first.id then visit = 0;
*for first order increment the visit variable (0 will go to 1 for first record);
if first.order then visit+1;
run;
Reply to point out a typo, the final 2 rows of order should be 7 in all tables.
@meriS wrote:
Reply to point out a typo, the final 2 rows of order should be 7 in all tables.
Fixed that for you 🙂
Close 🙂
proc sort have; by id order; run;
data want;
set have;
by id order;
*set to 0 for first ID;
if first.id then visit = 0;
*for first order increment the visit variable (0 will go to 1 for first record);
if first.order then visit+1;
run;
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.