Count rows by id

[frakje]

Hello

I need to translate this STATA code into SAS: bys cpr protokol (dt_registrering): gen index1 = _n if year(dt_vurdering)< $year

A subset of my dataset looks like this:

cpr       protokol      dt_registration        dt_assesment

100      fys               05feb2016             21jan2016

100      fys               27apr2017             17jan2017

100      fys               27apr2018             17jan2018

100      ergo            23feb2016              10jan2016

100      ergo            19jan2017               5jan2017

100      ergo            19jan2018               5jan2018

200     fys                23feb2016              10jan2016

200     fys                24apr2017              15jan2017

200     ergo             19mar2016              7jan2016

200     ergo             20mar2017               12jan2017

I want to make  a new variable that counts the number of assesments before year 2018 group by CPR and Protokol.

Something like this:

cpr       protokol      dt_registration        dt_assesment   index1

100      fys               05feb2016             21jan2016          1

100      fys               27apr2017             17jan2017          2

100      fys               27apr2018             17jan2018          .

100      ergo            23feb2016              10jan2016         1

100      ergo            19jan2017               5jan2017           2

100      ergo            19jan2018               5jan2018           .

200     fys                23feb2016              10jan2016         1

200     fys                24apr2017              15jan2017         2

200     ergo             19mar2016              7jan2016          1

200     ergo             20mar2017               12jan2017        2

Thank you

Kind regards Frank

[novinosrin · Accepted solution]

Hi @frakje   See whether my understanding is correct using the below. Thanks!

data have;
input cpr       protokol $     (dt_registration        dt_assesment) (:date9.);
format dt_registration        dt_assesment date9.;
cards;
100      fys               05feb2016             21jan2016
100      fys               27apr2017             17jan2017
100      fys               27apr2018             17jan2018
100      ergo            23feb2016              10jan2016
100      ergo            19jan2017               5jan2017
100      ergo            19jan2018               5jan2018
200     fys                23feb2016              10jan2016
200     fys                24apr2017              15jan2017
200     ergo             19mar2016              7jan2016
200     ergo             20mar2017               12jan2017
;
data want;
 set have;
 by cpr protokol notsorted;
 if first.protokol then count=1;
 else if year(dt_registration)<2018 then count+1;
 else count=.;
run;

 /*Or a safer bet below*/

data want;
 set have;
 by cpr protokol notsorted;
 if first.protokol then count=0;
 if year(dt_registration)<2018 then count+1;
 else count=.;
run;

[tomrvincent]

What have you tried so far?