topic Re: Convert Clustered Counts to Dummy Coding in SAS Programming

Convert Clustered Counts to Dummy Coding

PhillipSherlock — Wed, 03 Oct 2018 14:02:23 GMT

I would like to convert data that is currently represented as a series of counts to dummy coding.

The following is similar to the data I have. However, the actual data has more organizations.

Organization	One_x	two_x	none_x	total_x	one_y	two_y	none_y	total_y
A	1	2	1	4	1	1	1	3

I would like to reshape the above data to have the following representation.

Organization	One_x	two_x	one_y	two_y
A	1	0	0	0
A	0	1	0	0
A	0	1	0	0
A	0	0	0	0
A	0	0	1	0
A	0	0	0	1
A	0	0	0	0

Thank you for your help!

Re: Convert Clustered Counts to Dummy Coding

PeterClemmensen — Wed, 03 Oct 2018 14:12:03 GMT

How do the none_x and total_x translate to the wanted table?

Re: Convert Clustered Counts to Dummy Coding

PhillipSherlock — Wed, 03 Oct 2018 14:15:57 GMT

Individuals from the X and Y groups have either received "one", "two", or 'none". Total_X is equal to the sum of one_x, two_x, and none_x. Thee total number of rows should be equal to the sum of total_x and total_y. Please let me know if that answered your question.

Re: Convert Clustered Counts to Dummy Coding

novinosrin — Wed, 03 Oct 2018 14:53:44 GMT

Are the variable names in your sample good representative of your real ?

The reason i am asking is because is it safe to take suffix _x for x groups and y groups respectively?

PS if you can clarify at best, I will try my idea that I have in mind

Re: Convert Clustered Counts to Dummy Coding

PhillipSherlock — Wed, 03 Oct 2018 14:54:41 GMT

If you can think of a way to make it work using those suffixes, then I will change the column names. Thank you for your help!

Re: Convert Clustered Counts to Dummy Coding

novinosrin — Wed, 03 Oct 2018 14:57:30 GMT

Lol That's smart way to put the ball back in my court eh? 🙂

Anyways, Good morning and I will brb. However, I'd suggest you to hang in there if somebody who knows proc iml well might just get you robust solution

Re: Convert Clustered Counts to Dummy Coding

novinosrin — Wed, 03 Oct 2018 15:21:47 GMT

data have;
input Organization $	One_x	two_x	none_x	total_x	one_y	two_y	none_y	total_y;
cards;
A	1	2	1	4	1	1	1	3
;
data want;
if _n_=1 then do;
 dcl hash H () ;
   h.definekey  ('var') ;
   h.definedata ("value") ;
   h.definedone () ;
end;
set have(drop=total:);
array t(*) One_x--none_y;
do _n_=1 to dim(t);
var=vname(t(_n_));
value=t(_n_);
rc=h.add();
t(_n_)=0;
end;
call missing(var,value);
do _n_=1 to dim(t);
h.find(key:vname(t(_n_)));
	do j=1 to value;
	t(_n_)=scan(vname(t(_n_)),1,'_' ) ne 'none';
	output;
	end;
t(_n_)=0;
end;
h.clear();
drop rc value j var;
run;