topic Re: Proc Transpose Help in SAS Programming

Proc Transpose Help

aap810 — Wed, 24 Oct 2018 15:34:10 GMT

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!

Re: Proc Transpose Help

novinosrin — Wed, 24 Oct 2018 15:48:21 GMT

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

Re: Proc Transpose Help

SuryaKiran — Wed, 24 Oct 2018 15:50:15 GMT

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;

Re: Proc Transpose Help

novinosrin — Wed, 24 Oct 2018 16:11:29 GMT

@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

Re: Proc Transpose Help

ballardw — Wed, 24 Oct 2018 19:52:48 GMT

@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.