topic Proc transpose only non-missing in SAS Procedures

Proc transpose only non-missing

rakeshvvv — Mon, 27 Oct 2014 19:14:08 GMT

I have a dataset with 5 variables A, B, C, D and E. A is the primary key , B and C are numeric variables, D and E are character variables.

I would like to transpose with A as the by variable in such a way that only non-missing numeric and character variable should be transposed for B,C,D,E.

I am using below code and I am getting period (.) for missing numeric variable in the transposed column.

PROC TRANSPOSE DATA=X OUT=Y

BY A;

VAR B C D E;

Run;

Re: Proc transpose only non-missing

ballardw — Mon, 27 Oct 2014 19:27:12 GMT

Post some example input data and what you want the output to look like.

Re: Proc transpose only non-missing

data_null__ — Mon, 27 Oct 2014 19:27:48 GMT

When both character(D,E) and numeric (B,C) variables are transposed the numeric variables are converted to character using the format associated with the variable. Since standard missing is usually displayed as . you get . in the new character value. Probably . with leading spaces.

If you want the missing numeric variables to have blank values after transposing use

OPTIONS MISSING=' ';

and then add this.

OUT=Y(where=(not missing(col1)))

Message was edited by: data _null_

Re: Proc transpose only non-missing

rakeshvvv — Mon, 27 Oct 2014 19:56:21 GMT

I am posting an example.... my output should look like z dataset instead of y...only non missing should be transposed.....

data x ;

set sashelp.class;

IF _N_ LT 5;

where sex='M';

IF AGE=14 THEN DO;

SEX="";

AGE=.;END;

run;

PROC TRANSPOSE DATA=X OUT=Y;

BY NAME;

VAR AGE SEX HEIGHT WEIGHT;

RUN;

DATA Z;

SET Y;

IF MISSING(COL1) OR COL1=' .' THEN DELETE;

RUN;