topic Re: Flag all rows that have the same ID and order number in SAS Programming

Flag all rows that have the same ID and order number

meriS — Tue, 17 Jan 2023 23:39:18 GMT

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.

Re: Flag all rows that have the same ID and order number

meriS — Tue, 17 Jan 2023 23:27:23 GMT

Reply to point out a typo, the final 2 rows of order should be 7 in all tables.

Re: Flag all rows that have the same ID and order number

Reeza — Tue, 17 Jan 2023 23:38:05 GMT

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;

Re: Flag all rows that have the same ID and order number

Reeza — Tue, 17 Jan 2023 23:39:42 GMT

@meriS wrote:

Reply to point out a typo, the final 2 rows of order should be 7 in all tables.

Fixed that for you 🙂

Re: Flag all rows that have the same ID and order number

meriS — Tue, 17 Jan 2023 23:42:25 GMT

Perfect, thank you!