topic Re: Tag a same number for each group in one column in New SAS User

Tag a same number for each group in one column

PhuongNguyen — Fri, 14 Dec 2018 01:47:47 GMT

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.

Re: Tag a same number for each group in one column

r_behata — Fri, 14 Dec 2018 01:58:32 GMT

It would be more helpful if you could show how your output should look like.

Re: Tag a same number for each group in one column

novinosrin — Fri, 14 Dec 2018 02:06:44 GMT

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;

Re: Tag a same number for each group in one column

PhuongNguyen — Fri, 14 Dec 2018 02:13:03 GMT

It works. Thank you @novinosrin

Re: Tag a same number for each group in one column

PhuongNguyen — Fri, 14 Dec 2018 02:29:13 GMT

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.

Re: Tag a same number for each group in one column

PhuongNguyen — Fri, 14 Dec 2018 02:32:53 GMT

Sorry, sort the dataset by CGscore then by year.

Proc sort have;

by CGscore year;

run;

Thank you