Proc Transpose Help

[aap810]

Hi,

I've been having trouble transposing a dataset. It looks like this:

Variable           N             PctN

Missing           133          7.0

Yes                 1300        15.0

How do I get a dataset that looks like this:

Var              N                PctN                Missing_N           Missing_PctN

Yes             1300            7.0                      133                      7.0

Thank you!

[novinosrin]

data have;
input Variable    $       N             PctN;
cards;
Missing           133          7.0
Yes                 1300        15.0
;


proc transpose data= have out=_have;
by variable;
var _numeric_;
run;
proc transpose data= _have out=want(drop=_name_) delimiter=_;
var col1;
id variable _name_;
run;

Notes:

Yes and missing are distinct obs and it's illogical to have var :yes in the final result

[SuryaKiran]

Do you need to do this by ID values, you may need a join

data have;
input ID Variable:$10. N PctN;
datalines;
1 Missing 133 7
1 Yes 1300 15
;
run;

proc sql;
select a.variable as var,a.N,a.PctN,b.N as Missing_N,b.PctN as Missing_PctN
from (select * from have where variable='Yes') a
inner join (select * from have where variable='Missing') as  b on a.id=b.id;
quit;

[novinosrin]

@SuryaKiran code is hard coding and will not scale if you many distinct values for variable beyond missing, yes etc. This will mandate looking for macro or call execute to generate statements after statements and personally playing with literal/constant is not convenient as opposed to playing with operands.

If you want to avoid double transpose and would like an automated merge code that will scale, refer to

https://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

[ballardw]

---

@aap810 wrote:

Hi,

I've been having trouble transposing a dataset. It looks like this:

Variable           N             PctN

Missing           133          7.0

Yes                 1300        15.0

How do I get a dataset that looks like this:

Var              N                PctN                Missing_N           Missing_PctN

Yes             1300            7.0                      133                      7.0

Thank you!

---

Let us go back a step and ask "where did that data set come from"? If this is the output of another procedure perhaps a change in the that step will provide options to create what you want.