Reverse Transpose

[Emilyab]

Hi,

I want to do some sort of reverse transpose so that I create observations
based on the value of a variable.

id var1 var2
1 1 2
2 1 2
3 1 3
4 1 4
5 2 1

and I need it to look like this:

id var1 var2
1 1 1
1 0 1

2 1 1
2 0 1

3 1 1
3 0 1
3 0 1

4 1 1
4 0 1
4 0 1
4 0 1

5 1 1
5 1 0

I have multiple pairs of variables to perform this action on; it would take
me hours and days even to do this by hand in Excel. I've searched all over
but to no avail (and I'm a SAS novice) so I am desperate. Please help! Thanks
so much in advance,
-E

[Mean Joe]

You can put multiple OUTPUT statements in a data step

data new(drop=v1 v2);
retain v1 v2;
set old;
v1=var1;
v2=var2;
if var1=1 then do;
var2=1; output; v2=v2-1;
do while (v2>0);
var1=0; output; v2=v2-1;
end;
end;
else if var2=1 then do;
var1=1; output; v1=v1-1;
do while (v1>0);
var2=0; output; v1=v1-1;
end;
end;
run;

[Emilyab]

Thanks so much for the reply, unfortunately this is giving me duplicates/incorrect counts for the values.


Ideally I don't want to separate it into two datasets, because then I have to deal with re-merging it back with the original set, which I then have to deduplicate records.

Any other suggestions?

[Mean Joe]

It's not separating into two datasets; it's just creating a new dataset so the old one is preserved. If you want, you can call "data new" as "data old", or whatever the name of your dataset is.

I don't know what your dataset looks like, or the rules you want to create your dataset by. You say there are multiple pairs of variables you need to work with, and I don't know what they are. The basic idea is to use multiple output statements, and you can use temporary variables (v1, v2 in the code) to count how many times you want to output a single ID.

[SPR]

Hello Emilyab,

This is a solution:
data i;
input id var1 var2;
datalines;
1 1 2
2 1 2
3 1 3
4 1 4
5 2 1
run;
data r;
set i;
if var1 <= var2 then do;
do i=1 to var1;
v1=1;
do j=1 to var2;
v2=1;
output;
v1=0;
end;
end;
end;
else do;
do i=1 to var2;
v2=1;
do j=1 to var1;
v1=1;
output;
v2=0;
end;
end;
end;
keep id v1 v2;
rename v1=var1 v2=var2;
run;
Sincerely,
SPR