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;
year | month | day | nbr | date | ifs_1 | ifs_2 | ifs_3 | ifs_4 | ifs_5 | ifs_6 | ifs_7 | ifs_8 | ifs_9 | ifs_10 | ifs_11 | ifs_12a | ifs_12b | ifs_12c | ifs_12d | ifs_12e | ifs_12f | ifs_12f_other | ifs_12g | ifs_12h | ifs_12i | ifs_13 | ifs_14a | ifs_14b | ifs_15 | ifs_16 | ifs_17 | ifs_18 | ifs_19 | ifs_20 | ifs_20a | ifs_23 | ifs_24 | ifs_25 | ifs_26 | ifs_27 | ifs_27a | ifs_28 | ifs_29 | ifs_30 | ifs_31 | ifs_12 | ifs_12f_1 | ifs_12f_2 | ifs_12f_3 | ifs_12f_4 | ifs_12f_5 | ifs_12f_6 | ONE | TWO | THREE | FOUR | FIVE |
2017 | 1 | 1 | 1 | 01Jan2017 | 2 | 3 | 5 | 3 | 5 | 5 | 4 | 5 | 5 | 2 | 5 | 3 | 1 | 4 | 4 | 0 | 1 | 2 | 3 | 3 | 6 | ||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 1 | 01Jan2017 | 5 | 5 | 5 | 5 | 5 | 5 | 4 | 5 | 3 | 4 | 5 | 5 | 5 | 5 | 5 | 0 | 0 | 1 | 2 | 12 | |||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 1 | 01Jan2017 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 4 | 5 | 5 | 5 | 5 | 3 | 4 | 0 | 0 | 0 | 1 | 2 | 12 | ||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 2 | 01Jan2017 | 1 | 3 | 4 | 1 | 5 | 4 | 5 | 4 | 5 | 5 | 3 | 1 | 1 | 1 | 1 | 6 | 0 | 2 | 3 | 4 | |||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 2 | 01Jan2017 | 5 | 5 | 5 | 5 | 5 | 5 | 4 | 5 | 4 | 5 | 5 | 1 | 1 | 0 | 0 | 2 | 9 | ||||||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 2 | 01Jan2017 | 2 | 2 | 3 | 3 | 4 | 2 | 4 | 4 | 4 | 3 | 2 | 3 | 2 | 1 | 3 | 0 | 1 | 5 | 5 | 4 | 0 | ||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 2 | 01Jan2017 | 3 | 3 | 3 | 3 | 3 | 2 | 3 | 3 | 3 | 3 | 2 | 3 | 2 | 2 | 3 | 1 | 1 | 4 | 11 | 0 | 0 | ||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 2 | 01Jan2017 | 5 | 4 | 5 | 5 | 5 | 4 | 3 | 4 | 4 | 5 | 4 | 5 | 4 | 5 | 5 | 0 | 0 | 0 | 1 | 6 | 8 | ||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 3 | 01Jan2017 | 3 | 5 | 4 | 4 | 5 | 4 | 4 | 4 | 4 | 4 | 4 | 5 | 1 | 3 | 4 | 1 | 0 | 2 | 9 | 3 | |||||||||||||||||||||||||||||||||
2017 | 1 | 1 | 3 | 01Jan2017 | 4 | 4 | 4 | 4 | 4 | 5 | 4 | 4 | 4 | 4 | 5 | 4 | 5 | 4 | 4 | 4 | 5 | 1 | 1 | 0 | 0 | 13 | 4 | ||||||||||||||||||||||||||||||
2017 | 1 | 1 | 3 | 01Jan2017 | 4 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 1 | 1 | 0 | 0 | 1 | 15 | |||||||||||||||||||||||||||||||
2017 | 1 | 1 | 3 | 01Jan2017 | 5 | 3 | 5 | 5 | 5 | 5 | 2 | 4 | Character | 4 | 5 | 5 | 4 | 3 | 3 | 2 | character | character | character | character | character | character |
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;
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;
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.
No problem, glad to help 🙂
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.