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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.