topic Add numbering sequence to order each ID in SAS Programming

Add numbering sequence to order each ID

mariko5797 — Mon, 06 Dec 2021 21:28:50 GMT

I'm not sure the best way to word this, but I essentially just want to add an ordering variable that will indicate the first visit (ORD = 1), second visit (ORD = 2), etc.

data have;
 input id visit @@;
 cards;
	1	101	1	201	1	301	1	401 
	2	101	2	201	2	401	
	3	201	3	301	3	401	
	4	101	4	301	4	401 
	5	301	5	401 
	6	201 
	7	101	7	201	7	301	7	401 
	8	101	8	401
 ;
run;

data have;
 input id visit @@;
 cards;
	1	101	1	201	1	301	1	401 
	2	101	2	201	2	401	
	3	201	3	301	3	401	
	4	101	4	301	4	401 
	5	301	5	401 
	6	201 
	7	101	7	201	7	301	7	401 
	8	101	8	401
 ;
run;

Re: Add numbering sequence to order each ID

mariko5797 — Mon, 06 Dec 2021 21:29:35 GMT

EDIT: data I want

data want;
 input id visit @@ ord;
 cards;
	1	101	1	1	201	2	1	301	3	1	401	4 
	2	101	1	2	201	2	2	401	3 
	3	201	1	3	301	2	3	401	3 
	4	101	1	4	301	2	4	401	3 
	5	301	1	5	401	2 
	6	201	1 
	7	101	1	7	201	2	7	301	3	7	401	4 
	8	101	1	8	401	2 
 ;
run;

Re: Add numbering sequence to order each ID

ballardw — Mon, 06 Dec 2021 23:28:28 GMT

One way:

/* assumes your data is sorted by ID*/
data want;
   set have;
   by id;
   retain ord;
   if first.id then ord=0;
   ord+1;
run;

If you data is not actually sorted by ID but just grouped use the option NOTSORTED on the By statement.

By creates automatic variables First. and Last. that you can use to identify if an observation is the first or last of a by group for conditional processing.

Retain creates a variable whose values are keep across iterations of the data step. The Ord+1; increments the value by one for each step. It is reset to 0 at the beginning of the group so the first value written is 1.