Hello Everyone , I have this DataSet :
regions | cities | Dates |
ML | FGG | 25/01/1998 |
ML | HGT | 25/02/2000 |
ML | HJK | 15/06/1999 |
ML | LOI | 02/05/2003 |
RS | DCF | 14/06/2000 |
RS | VG | 03/04/1992 |
RS | BBVB | 03/10/2003 |
RS | DFR | 11/07/2005 |
GT | ZAS | 03/08/2006 |
GT | EWS | 16/02/2010 |
GT | WE | 17/11/2011 |
& What i want to do , is to assign an incremental number indexing to a the key column , like my output would be like this :
regions | cities | Dates |
1 | FGG | 25/01/1998 |
1 | HGT | 25/02/2000 |
1 | HJK | 15/06/1999 |
1 | LOI | 02/05/2003 |
2 | DCF | 14/06/2000 |
2 | VG | 03/04/1992 |
2 | BBVB | 03/10/2003 |
2 | DFR | 11/07/2005 |
3 | ZAS | 03/08/2006 |
3 | EWS | 16/02/2010 |
3 | WE | 17/11/2011 |
Any Idea , how i could eventually do that ? , any suggestion would be much appreciated , thank you.
Do like this
data have;
input regions $ 1-2 cities $ 4-7 Dates :ddmmyy10.;
format dates ddmmyy10.;
datalines;
ML FGG 25/01/1998
ML HGT 25/02/2000
ML HJK 15/06/1999
ML LOI 02/05/2003
RS DCF 14/06/2000
RS VG 03/04/1992
RS BBVB 03/10/2003
RS DFR 11/07/2005
GT ZAS 03/08/2006
GT EWS 16/02/2010
GT WE 17/11/2011
;
data want(drop=regions);
set have;
by regions notsorted;
if first.regions then c+1;
rename c=regions;
run;
Result:
cities dates regions FGG 25/01/1998 1 HGT 25/02/2000 1 HJK 15/06/1999 1 LOI 02/05/2003 1 DCF 14/06/2000 2 VG 03/04/1992 2 BBVB 03/10/2003 2 DFR 11/07/2005 2 ZAS 03/08/2006 3 EWS 16/02/2010 3 WE 17/11/2011 3
Do like this
data have;
input regions $ 1-2 cities $ 4-7 Dates :ddmmyy10.;
format dates ddmmyy10.;
datalines;
ML FGG 25/01/1998
ML HGT 25/02/2000
ML HJK 15/06/1999
ML LOI 02/05/2003
RS DCF 14/06/2000
RS VG 03/04/1992
RS BBVB 03/10/2003
RS DFR 11/07/2005
GT ZAS 03/08/2006
GT EWS 16/02/2010
GT WE 17/11/2011
;
data want(drop=regions);
set have;
by regions notsorted;
if first.regions then c+1;
rename c=regions;
run;
Result:
cities dates regions FGG 25/01/1998 1 HGT 25/02/2000 1 HJK 15/06/1999 1 LOI 02/05/2003 1 DCF 14/06/2000 2 VG 03/04/1992 2 BBVB 03/10/2003 2 DFR 11/07/2005 2 ZAS 03/08/2006 3 EWS 16/02/2010 3 WE 17/11/2011 3
Thank You a lot , really appreciated it .
Anytime 🙂
Or just:
data want (drop = _) ;
set have (rename=regions=_) ;
regions ++ _ ne lag (_) ;
run ;
Kind regards
Paul D.
Hi everyone. I am new here. Thanks for the information
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.