Convert Clustered Counts to Dummy Coding

[PhillipSherlock]

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!

[PeterClemmensen]

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

[PhillipSherlock]

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. 

[novinosrin]

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

[PhillipSherlock]

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!

[novinosrin]

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

[novinosrin · Accepted solution]

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;