- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi, say I have a dataset which descends by year. I want to create a group variable (want) such that it counts 1, 2, 3... etc. per year like so:
data have; infile datalines delimiter=' '; input year want; datalines; 2020 1 2020 1 2019 2 2019 2 2019 2 2018 3 2018 3 2017 4 2017 4 2017 4 ;
any help would be appreciated. Thanks!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Just use a retained variable. It is simplified by using the SUM statement. Since the years are sorted in descending order include the DESCENDING keyword in the BY statement. Take advantage of the fact that SAS creates the boolean FIRST. by group flags using 1 for TRUE and 0 for FALSE.
data have;
input year @@;
datalines;
2020 2020 2019 2019 2019 2018 2018 2017 2017 2017
;
data want;
set have;
by descending year;
group+first.year;
run;
proc print;
run;
Results
Obs year group 1 2020 1 2 2020 1 3 2019 2 4 2019 2 5 2019 2 6 2018 3 7 2018 3 8 2017 4 9 2017 4 10 2017 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data want;
set have;
by year;
if _n_=1 the want2=0;
if first.year then want2+1;
run;
@richart wrote:
Hi, say I have a dataset which descends by year. I want to create a group variable (want) such that it counts 1, 2, 3... etc. per year like so:
data have; infile datalines delimiter=' '; input year want; datalines; 2020 1 2020 1 2019 2 2019 2 2019 2 2018 3 2018 3 2017 4 2017 4 2017 4 ;
any help would be appreciated. Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
thanks, this works too but I think you need to add 'descending' before year.. else it wasn't working for me
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Just use a retained variable. It is simplified by using the SUM statement. Since the years are sorted in descending order include the DESCENDING keyword in the BY statement. Take advantage of the fact that SAS creates the boolean FIRST. by group flags using 1 for TRUE and 0 for FALSE.
data have;
input year @@;
datalines;
2020 2020 2019 2019 2019 2018 2018 2017 2017 2017
;
data want;
set have;
by descending year;
group+first.year;
run;
proc print;
run;
Results
Obs year group 1 2020 1 2 2020 1 3 2019 2 4 2019 2 5 2019 2 6 2018 3 7 2018 3 8 2017 4 9 2017 4 10 2017 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello @richart ,
This Communities Library article is answering your question :
How do I add a row number to a table in SAS code?
Posted 11-18-2015 08:14 AM | by Patrick (126287 views)
https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-add-a-row-number-to-a-table-in-SAS-c...
Koen