data WORK.HAVE;
infile datalines dsd truncover;
input RIC:$6. year:BEST4. CGscore:BEST20. eps:BEST10.;
format year BEST4. CGscore BEST20. eps BEST10.;
datalines;
AAM.HM 2013 67.63 141
AAM.HM 2014 70.17 192
AAM.HM 2014 70.17 26
AAM.HM 2015 67.8 30
AAM.HM 2016 67.8 43
ABT.HM 2013 79.15 1331
ABT.HM 2013 79.15 1269
ABT.HM 2014 78.81 1959
ABT.HM 2014 78.81 1787
ABT.HM 2014 78.81 1768
ABT.HM 2015 77.12 1381
ABT.HM 2016 76.61 1083
ACL.HM 2014 72.54 98
ACL.HM 2014 72.54 289
ACL.HM 2016 74.92 264
ACL.HM 2016 74.92 40
ACL.HM 2016 74.92 375
AGF.HM 2013 70.85 607
AGF.HM 2013 70.85 492
AGF.HM 2014 64.41 -516
;;;;
Hello,
I have the dataset as above, and want to know how many RIC there are by placing a same tag (number or character) within a group of RIC, created in a new column. Simply I want to add another column counting 1 for all AAM.HM of column RIC, and 2 for all ABT.HM of RIC, 3 for all ACL.HM, and so on. Please help.
Thank you.
data WORK.HAVE;
infile datalines truncover;
input RIC:$6. year CGscore eps;
format year BEST4. CGscore BEST20. eps BEST10.;
datalines;
AAM.HM 2013 67.63 141
AAM.HM 2014 70.17 192
AAM.HM 2014 70.17 26
AAM.HM 2015 67.8 30
AAM.HM 2016 67.8 43
ABT.HM 2013 79.15 1331
ABT.HM 2013 79.15 1269
ABT.HM 2014 78.81 1959
ABT.HM 2014 78.81 1787
ABT.HM 2014 78.81 1768
ABT.HM 2015 77.12 1381
ABT.HM 2016 76.61 1083
ACL.HM 2014 72.54 98
ACL.HM 2014 72.54 289
ACL.HM 2016 74.92 264
ACL.HM 2016 74.92 40
ACL.HM 2016 74.92 375
AGF.HM 2013 70.85 607
AGF.HM 2013 70.85 492
AGF.HM 2014 64.41 -516
;;;;
data want;
set have;
by ric ;
if first.ric then c+1;
run;
data WORK.HAVE;
infile datalines truncover;
input RIC:$6. year CGscore eps;
format year BEST4. CGscore BEST20. eps BEST10.;
datalines;
AAM.HM 2013 67.63 141
AAM.HM 2014 70.17 192
AAM.HM 2014 70.17 26
AAM.HM 2015 67.8 30
AAM.HM 2016 67.8 43
ABT.HM 2013 79.15 1331
ABT.HM 2013 79.15 1269
ABT.HM 2014 78.81 1959
ABT.HM 2014 78.81 1787
ABT.HM 2014 78.81 1768
ABT.HM 2015 77.12 1381
ABT.HM 2016 76.61 1083
ACL.HM 2014 72.54 98
ACL.HM 2014 72.54 289
ACL.HM 2016 74.92 264
ACL.HM 2016 74.92 40
ACL.HM 2016 74.92 375
AGF.HM 2013 70.85 607
AGF.HM 2013 70.85 492
AGF.HM 2014 64.41 -516
;;;;
data want;
set have;
by ric ;
if first.ric then c+1;
run;
It works. Thank you @novinosrin
Hi, and now I sort the dataset by CGscore. And also want to add another column, counting 1 for all same first RICs whichever come first after the sorting, and 2 for all next same RICs,............. Can you help @novinosrin @r_behata ? Thank you.
Sorry, sort the dataset by CGscore then by year.
Proc sort have;
by CGscore year;
run;
Thank you
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.