CODEWITHSUNDEEP
sqlsas
user20036996
user20036996

Reputation: 1

Create a running number based on two columns

I am working on SAS and the data I have is this using SAS data step or proc sql...

ID date
1 1-jan-2010
1 1-jan-2010
1 1-jan-2014
2 1-jan-2011
2 1-jan-2011
3 1-jan-2011
3 1-jan-2019
3 1-jan-2020
3 1-jan-2021

I want to create a new var called var_new which will take a running number for each group of ID and date. I don´t want to delete the duplicates.

ID date var_new
x 1-jan-2010 1
x 1-jan-2010 1
x 1-jan-2014 2
y 1-jan-2011 1
y 1-jan-2011 1
y 1-jan-2011 1
z 1-jan-2019 1
z 1-jan-2020 2
z 1-jan-2021 3
z 1-jan-2022 4

Please help!!

Upvotes: 0

Views: 34

Answers (1)

PeterClemmensen
PeterClemmensen

Reputation: 4947

Try this

data have;
input ID date :anydtdte.;
format date date9.;
datalines;
1 1-jan-2010
1 1-jan-2010
1 1-jan-2014
2 1-jan-2011
2 1-jan-2011
3 1-jan-2011
3 1-jan-2019
3 1-jan-2020
3 1-jan-2021
;

data want;
   set have;
   by ID date;
   if first.ID then var_new = 0;
   if first.date then var_new + 1;
run;

Upvotes: 3

Related Questions