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;
Post some example input data and what you want the output to look like.
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;
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_
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.