BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Viresh
Calcite | Level 5

Hello all,

 

Would really appreciate if anyone could help.  I am trying to count distinct numeric values by certain columns and then create a column at the end with that distinct count.  Below is an example in excel, I have tried pretty much everything and cant seem to make it work.  I am trying to count the all the one's, two's, three's, four's and five's starting from column - ifs_1 and put the count in each prospective column in bold at the end.  Below is the code which I tried.

 

data want;

set having1;

array ifs_(*) ifs_1-ifs_31;

ifs_Count3=0;

do i=2 to 31;

if ifs_(i)=5 then ifs_Count3+1;

end;

run;

 

yearmonthdaynbrdateifs_1ifs_2ifs_3ifs_4ifs_5ifs_6ifs_7ifs_8ifs_9ifs_10ifs_11ifs_12aifs_12bifs_12cifs_12difs_12eifs_12fifs_12f_otherifs_12gifs_12hifs_12iifs_13ifs_14aifs_14bifs_15ifs_16ifs_17ifs_18ifs_19ifs_20ifs_20aifs_23ifs_24ifs_25ifs_26ifs_27ifs_27aifs_28ifs_29ifs_30ifs_31ifs_12ifs_12f_1ifs_12f_2ifs_12f_3ifs_12f_4ifs_12f_5ifs_12f_6ONETWOTHREEFOURFIVE
201711101Jan201723535     54  5   5 2 5 3144   0                12336
201711101Jan201755555     54  5   3 4 5 5555                    001212
201711101Jan201755555     55  5   4 5 5 5534   0                001212
201711201Jan201713415     45  4   5   5 3111   1                60234
201711201Jan201755555     54  5   4   5 5      1                10029
201711201Jan201722334     24  4   4 3 2 3213   0                15540
201711201Jan201733333     23  3   3 3 2 3223   1                141100
201711201Jan201754555     43  4   4 5 4 5455   0                00168
201711301Jan201735445     44  4   4 4 4 5134                    10293
201711301Jan201744444     54  4   4 4 5 45444 51                100134
201711301Jan201745555     55  5   5 5 5 55555  1                100115
201711301Jan201753555     52  4 Character 4 5 5 4332              charactercharactercharactercharactercharactercharacter     
1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

For demonstration purposes, I extracted a subset of your data. 

 

You can do something like this

 

data having1;
input year month day nbr date:date9. ifs_1 ifs_2 ifs_3 ifs_4 ifs_5;
datalines;
2017 1 1 1 01Jan2017 2 3 5 3 5
2017 1 1 1 01Jan2017 5 5 5 5 5
2017 1 1 1 01Jan2017 5 5 5 5 5
2017 1 1 2 01Jan2017 1 3 4 1 5
2017 1 1 2 01Jan2017 5 5 5 5 5
2017 1 1 2 01Jan2017 2 2 3 3 4
2017 1 1 2 01Jan2017 3 3 3 3 3
2017 1 1 2 01Jan2017 5 4 5 5 5
2017 1 1 3 01Jan2017 3 5 4 4 5
2017 1 1 3 01Jan2017 4 4 4 4 4
2017 1 1 3 01Jan2017 4 5 5 5 5
2017 1 1 3 01Jan2017 5 3 5 5 5
;

data want;
   set having1;
   array ifs_(*) ifs_1-numeric-ifs_5;
   one=countc(cats(of ifs_[*]), '1');
   two=countc(cats(of ifs_[*]), '2');
   three=countc(cats(of ifs_[*]), '3');
   four=countc(cats(of ifs_[*]), '4');
   five=countc(cats(of ifs_[*]), '5');
run;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

For demonstration purposes, I extracted a subset of your data. 

 

You can do something like this

 

data having1;
input year month day nbr date:date9. ifs_1 ifs_2 ifs_3 ifs_4 ifs_5;
datalines;
2017 1 1 1 01Jan2017 2 3 5 3 5
2017 1 1 1 01Jan2017 5 5 5 5 5
2017 1 1 1 01Jan2017 5 5 5 5 5
2017 1 1 2 01Jan2017 1 3 4 1 5
2017 1 1 2 01Jan2017 5 5 5 5 5
2017 1 1 2 01Jan2017 2 2 3 3 4
2017 1 1 2 01Jan2017 3 3 3 3 3
2017 1 1 2 01Jan2017 5 4 5 5 5
2017 1 1 3 01Jan2017 3 5 4 4 5
2017 1 1 3 01Jan2017 4 4 4 4 4
2017 1 1 3 01Jan2017 4 5 5 5 5
2017 1 1 3 01Jan2017 5 3 5 5 5
;

data want;
   set having1;
   array ifs_(*) ifs_1-numeric-ifs_5;
   one=countc(cats(of ifs_[*]), '1');
   two=countc(cats(of ifs_[*]), '2');
   three=countc(cats(of ifs_[*]), '3');
   four=countc(cats(of ifs_[*]), '4');
   five=countc(cats(of ifs_[*]), '5');
run;
Viresh
Calcite | Level 5

Thank you so very much draycut!!  you are awesome.  It works like a charm.  Thank you so much, I had been pulling my hair but this solves my issue.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1682 views
  • 1 like
  • 2 in conversation