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!
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
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!
thanks, this works too but I think you need to add 'descending' before year.. else it wasn't working for me
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
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
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.