I want to add a row number to a table. In some versions of SQL you can use PARTITION BY to add a row number and track that row counter over several BY group variables. In SAS, how can I add a row counter using more than 1 variable?
If it's simply about the row number then SAS has an automatic variable _N_ which gives you exactly that. If you want to store this number permanently in a new variable then it's as simple as:
data want; set sashelp.class; rownum=_n_; run;
If you're more after creating a group identifier then something like below should work:
data want; set have; by var1 var2; /* data set have must be sorted by var1 var2 */ if first.var2 then group_id+1; run;
SAS syntax "group_id+1" as used in above code retains the variable so you don't need an additional RETAIN statement.
Or if you're after a counter within a group:
data want; set have; by var1 var2; /* data set have must be sorted by var1 var2 */ if first.var2 then do; group_id+1; counter_in_group=1; end; else counter_in_group+1; run;