I want to add a rownumber to a table. In sql you can do a partition by to add a rownumber and start counting the rownumber on several BY variables.
I have found this code:
Data myTable;
Set myTable;
By Var1;
if first. Var1 then n=1;
else n+1;
run;
How can I add a rownumber using more than 1 variable?
Not sure that I understand what you mean by "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;
Your code doesn't seem to be correct. There is a space after the period in first., which shouldn't work, and you have no retain statement. Can you also post example test data (as a datastep, just a couple of lines of data) and required output, so we can see clearly what you want. At a guess:
data mytable; set mytable; by var1 var2; retain n; if first.var2 then n=1; else n=n+1; run;
This will create an n variable as a count within var2. Note needs to be sorted first.
Thanks for your answer. This is exactly what I needed.
Not sure that I understand what you mean by "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;
SAS will generate FIRST. and LAST. flag variables for every variable in your BY statement.
If it takes more than one variable to uniquely identify a group then you would just want to use flags for the last of them.
For example this program will generate a unique id that increments for each new by group and also an sequence number that starts over at one within each group.
data want ;
set have ;
by state city ;
city_id + first.city ;
if first.city then seqno=0;
seqno+1;
run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.