create a count variable by group?

[richart]

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!

[Reeza]

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!

---

[richart]

thanks, this works too but I think you need to add 'descending' before year.. else it wasn't working for me

[Tom · Accepted solution]

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

[sbxkoenk]

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