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_
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.