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!
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;
How do the none_x and total_x translate to the wanted table?
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.
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.