topic Re: Count unique numeric values across columns and add the counts column at end in SAS Programming

Count unique numeric values across columns and add the counts column at end

Viresh — Sun, 09 Sep 2018 06:09:01 GMT

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

Re: Count unique numeric values across columns and add the counts column at end

PeterClemmensen — Sun, 09 Sep 2018 13:09:23 GMT

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;

Re: Count unique numeric values across columns and add the counts column at end

Viresh — Sun, 09 Sep 2018 18:17:31 GMT

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.

Re: Count unique numeric values across columns and add the counts column at end

PeterClemmensen — Sun, 09 Sep 2018 18:50:12 GMT

No problem, glad to help 🙂