topic count of unique values across many columns by class in New SAS User

count of unique values across many columns by class

CJ_Jackson — Mon, 11 Feb 2019 05:01:45 GMT

If I have the following data

DATA raw;
	INPUT group $ var1 $ var2 $;
	CARDS;
	A Y Y
	A Y N
	A N N
	A Y Y
	A Y Y
	B N N
	B N N
	B N N
	B Y N
	;
run;

And I want my result to look like this:

Group value var1 var2
  A     Y    4    3
  A     N    1    2
  B     Y    1    0
  B     N    3    4

Assume that there are many more variable columns in the actual data set. How do I do this without just using proc tabulate on each variable and then manually joining the result tables together?

Re: count of unique values across many columns by class

novinosrin — Mon, 11 Feb 2019 05:08:56 GMT

many columns is "how many"? (can you give us a rough estimate?)

Re: count of unique values across many columns by class

CJ_Jackson — Mon, 11 Feb 2019 05:09:49 GMT

about 20 or so. Essentially I want to avoid manually doing this.

Re: count of unique values across many columns by class

novinosrin — Mon, 11 Feb 2019 05:19:06 GMT

I totally agree with you. Just thinking of how best that can be done

Re: count of unique values across many columns by class

novinosrin — Mon, 11 Feb 2019 05:41:24 GMT

see if this helps

DATA raw;
	INPUT group $ var1 $ var2 $;
	CARDS;
	A Y Y
	A Y N
	A N N
	A Y Y
	A Y Y
	B N N
	B N N
	B N N
	B Y N
	;
run;
data t;
set raw;
array t(*) var:;
do i= 1 to dim(t);
vn=vname(t(i));
val=t(i);
output;
end;
keep group vn val;
run;

proc sql;
create table w2 as
select group, vn,val,count(val) as c
from t
group by group, vn,val
order by group ,val;
quit;

proc transpose data=w2 out=want(drop=_name_);
by group val;
var c;
id vn ;
run;

Re: count of unique values across many columns by class

CJ_Jackson — Mon, 11 Feb 2019 05:53:12 GMT

Yeah, this works. Thanks. So basically I have to change the original data set from wide to long. I wonder if proc transpose could be used to achieve that.

Re: count of unique values across many columns by class

Astounding — Mon, 11 Feb 2019 05:55:46 GMT

First normalize the data.

Data want:
set have;
array varieties [20] /* add var names if not var1-var20 */;
Do k=1 to 20;
value = var[k];
variable = vname(var[k]);
output;
end;
run;

Now Proc Tabulate can easily generate a combined report.

proc tabulate data=want;
class variable value group;
tables group*value, variable;
run;

Re: count of unique values across many columns by class

Astounding — Mon, 11 Feb 2019 05:57:19 GMT

Array var not varieties... hate these handhelds!!!