topic Re: create a count variable by group? in SAS Procedures

create a count variable by group?

richart — Wed, 25 May 2022 01:08:55 GMT

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!

Re: create a count variable by group?

Reeza — Wed, 25 May 2022 01:30:01 GMT

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!

Re: create a count variable by group?

Tom — Wed, 25 May 2022 02:14:25 GMT

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

Re: create a count variable by group?

sbxkoenk — Wed, 25 May 2022 12:23:33 GMT

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-code/ta-p/235215

Koen

Re: create a count variable by group?

richart — Thu, 26 May 2022 22:40:34 GMT

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