topic Re: proc transpose issue in SAS Procedures

proc transpose issue

saspert — Fri, 25 Mar 2011 20:38:15 GMT

Hello,
I am trying to transpose this dataset
Type RLTN PARTICIPANTS
Distribution Center 0 4
Distribution Center 1 2
Headquarters 0 8
Headquarters 1 3
Retail Store 0 4
Retail Store 1 2

to this -
Type 0 1
Distribution Center 4 2
Headquarters 8 3
Retail Store 4 2

using this code -

PROC TRANSPOSE DATA=COMP OUT=COMP_TR
(RENAME=(0=PART_EMPLOYEE 1=PART_SPOUSES)) ;
BY TYPE;
ID RLTN;
VAR participants;
RUN;

My issue is that I dont have a valid Rename option because the field names in the transposed dataset are numeric. Is there an automatic way of doing it?

The only other option I see is to change 0 and 1 to Employee and Spouse before the transpose. I am looking if there is a way to avoid that.

Thanks,
saspert

Re: proc transpose issue

data_null__ — Sat, 26 Mar 2011 11:31:44 GMT

For ID variables that don't produce SAS names PROC TRANSPOSE adds _ to the value, see docs for details.

So your just need to modify your RENAME option. I would use the method in the second TRANSPOSE with FORMAT for RLTN.

[pre]
data COMP;
input Type &$16. RLTN PARTICIPANTS;
cards;
Distribution Center 0 4
Distribution Center 1 2
Headquarters 0 8
Headquarters 1 3
Retail Store 0 4
Retail Store 1 2
;;;;
run;
PROC TRANSPOSE DATA=COMP OUT=COMP_TR
(RENAME=(_0=PART_EMPLOYEE _1=PART_SPOUSES)) ;
BY TYPE;
ID RLTN;
VAR participants;
RUN;
proc print;
run;

proc format;
value rltn 0='PartEmployee' 1='PartSpouses';
run;

PROC TRANSPOSE DATA=COMP OUT=COMP_TR(drop=_name_);
BY TYPE;
ID RLTN;
VAR participants;
format rltn rltn.;
RUN;
proc print;
run;
[/pre]

Re: proc transpose issue

saspert — Tue, 29 Mar 2011 19:53:44 GMT

Thank you Data _null_.

Do you know if there is an option to automatically convert missing values to 0? Suppose I have the original dataset like this -

Male A 10
Female A 5
Male B 10

When I transpose this I get
A B
Male 10 10
Female 5 .

I want the Female-B cell to be converted to 0 rather than missing value. The reason behind this is that I have several rows that are being transposed to columns.

Regards,
saspert. Message was edited by: saspert

Re: proc transpose issue

data_null__ — Wed, 30 Mar 2011 16:41:29 GMT

PROC STDIZE is pretty easy.

[pre]
data test;
input gender $ id $ y;
cards;
Male A 10
Female A 5
Male B 10
;;;;
run;
proc transpose out=tall;
by id;
var y;
id gender;
run;
proc stdize out=zeroed reponly missing=0;
var _numeric_;
run;
proc print;
run;
[/pre]

Re: proc transpose issue

saspert — Wed, 30 Mar 2011 17:44:04 GMT

Great ! Thank you Data _null_. Proc STDIZE is new to me. It will suit my purpose (even though I am not familiar with it 😄 ).

To your suggestion of using _0 and _1, I am finding using the Prefix option may save me some programming time. I was not familiar with the prefix option until I started researching. But thank you for your suggestion.

I have a related issue. There are no errors but I keep getting the warning that some of the drop variables are never referenced -

113 PROC TRANSPOSE DATA=COUNT OUT=COUNT_TR (DROP=_LABEL_ _NAME_) PREFIX=COUNT ;
114 BY TYPE;
115 ID RELATIONSHIP;
116 VAR COUNT;
117 RUN;

WARNING: The variable _LABEL_ in the DROP, KEEP, or RENAME list has never been referenced. Message was edited by: saspert

Re: proc transpose issue

WarrenR_SAS — Wed, 30 Mar 2011 21:36:22 GMT

The _LABEL_ variable is only created by PROC TRANSPOSE if there are variable labels in the input data set for a variable listed in the VAR statement.

If none of those variables have labels, _LABEL_ is not created and therefore cannot be dropped. Removing _LABEL_ from the DROP= option would get rid of the warning.

If, for some reason, you must leave _LABEL_ in the DROP= option, you would get the same effect by adding a LABEL statement in the the PROC TRANSPOSE step, specifying any non-blank label for any variable in your VAR statement. The _LABEL_ column will be created, then dropped. A bit odd, but no harm, no foul.

-Warren Repole, SAS

Re: proc transpose issue

saspert — Wed, 30 Mar 2011 23:44:32 GMT

Hi Warren,
Thank you for your suggestion. The reason I had drop= _label_ is because the _label_ field shows up in the resulting transposed dataset. Now if I remove the _label_ from the drop = list, the warning goes away but I see the field in the tranposed dataset.

Regards,
saspert