Help using Base SAS procedures

Proc transpose only non-missing

Reply
Frequent Contributor
Posts: 145

Proc transpose only non-missing

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;

Super User
Posts: 10,550

Re: Proc transpose only non-missing

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

Frequent Contributor
Posts: 145

Re: Proc transpose only non-missing

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;

Respected Advisor
Posts: 3,777

Re: Proc transpose only non-missing

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_

Ask a Question
Discussion stats
  • 3 replies
  • 658 views
  • 3 likes
  • 3 in conversation