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 🙂
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!
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.
Ready to level-up your skills? Choose your own adventure.