Preventing PROC TRANSPOSE from truncating new variable names generated from ID variable

[JacquesR]

Hi all

This help page:

https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000063668.htm

States: "When a variable name is being formed in the transposed (output) data set, the formatted values of all listed ID variables will be concatenated in the same order that the variables are listed on the ID statement."

This is most frustrating, and I need to find a way to prevent this from happening.

Imagine I have a dataset 'demo' with values for Gender of Male Female Indeterminate and MISSING. Then I run this code:

    PROC FREQ
        NOPRINT
        DATA=demo;
        TABLE PersonID *Gender /out=Counts(DROP=PERCENT);
    RUN;
    /* Get the full list of options in the Counts */
    PROC SQL
        NOPRINT;
        SELECT DISTINCT Gender
            INTO :compvars 
            SEPARATED BY ' ' 
            FROM Counts;
    QUIT;
    %PUT &=compvars.;
    PROC TRANSPOSE
        DATA=Counts
        OUT=Combinations (DROP=_NAME_ _LABEL_);
        ID Gender;
        BY PersonID;
        VAR COUNT;
    RUN;

The variables in the Combinations dataset are:

PersonID Female Male Indeter MISSING

But I need them to be 

PersonID Female Male Indeterminate MISSING

for later code.

My mind tells me it should be simple enough, but I can't seem to find anything in the documentation to tell me how to do it.

Note the code above I did pull out all the options into a macro variable, thinking I could possible rename the variables later, but I'm not sure how I want to go about that, because eventually, this will end up in a macro and the example above of Gender could become anything.

Thanks

[ballardw · Accepted solution]

How about some details on the variable GENDER. Such as the length and format of the variable before transpose.

What does your log show for the output from your :

%PUT &=compvars.;

Since you did not put a formatted value with SQL into the macro variable then that should display the raw values.

I suspect that somewhere along the line your gender variable is only 7 characters in length and if there is a format that is supposed to display a longer value it is not available to the SAS session or you are looking at the result in something with a narrow column not showing the full value.

When I make a data set where the length of gender is 7 characters and assign a format that displays a longer value it transposes just fine with 'Indeterminate'as the new variable name.

proc format;
value $gen
'Indeter' ='Indeterminate'
;
run;
data have;
   input idvar gender :$7. value   ;
   format gender $gen.;
datalines;
1 Female    27
1 Male      13
1 Indeter 8 
1 MISSING   4
;

proc transpose data=have out=trans;
   by idvar;
   id gender;
   var value;
run;

---

@JacquesR wrote:

Hi all

This help page:

https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000063668.htm

States: "When a variable name is being formed in the transposed (output) data set, the formatted values of all listed ID variables will be concatenated in the same order that the variables are listed on the ID statement."

This is most frustrating, and I need to find a way to prevent this from happening.

Imagine I have a dataset 'demo' with values for Gender of Male Female Indeterminate and MISSING. Then I run this code:

    PROC FREQ
        NOPRINT
        DATA=demo;
        TABLE PersonID *Gender /out=Counts(DROP=PERCENT);
    RUN;
    /* Get the full list of options in the Counts */
    PROC SQL
        NOPRINT;
        SELECT DISTINCT Gender
            INTO :compvars 
            SEPARATED BY ' ' 
            FROM Counts;
    QUIT;
    %PUT &=compvars.;
    PROC TRANSPOSE
        DATA=Counts
        OUT=Combinations (DROP=_NAME_ _LABEL_);
        ID Gender;
        BY PersonID;
        VAR COUNT;
    RUN;

The variables in the Combinations dataset are:

PersonID Female Male Indeter MISSING

But I need them to be 

PersonID Female Male Indeterminate MISSING

for later code.

My mind tells me it should be simple enough, but I can't seem to find anything in the documentation to tell me how to do it.

Note the code above I did pull out all the options into a macro variable, thinking I could possible rename the variables later, but I'm not sure how I want to go about that, because eventually, this will end up in a macro and the example above of Gender could become anything.

Thanks

---

[JacquesR]

Thanks for your response. That helped me see the blindingly obvious.

I was using a format to make the missing values "visible" and so I just dropped the format, and hard-coded an actual value in the place of missing values

if missing(gender) then gender='_missing_';

Without the format, it works as it should. I should have seen that, but I think I got myself thinking in circles and not seeing the obvious.