topic Re: How do I get variables copied into all the new rows when transposing from wide to long? in SAS Programming

How do I get variables copied into all the new rows when transposing from wide to long?

Geoghegan — Wed, 06 Jul 2022 18:28:32 GMT

I am trying to transpose from wide to long because I have 30 variables (dx1-dx30) which I'd like to put into one column with a different row for each dx variable. I want all other variables in that dataset copied for each row for those dx variables. I was able to do most of this (my dx variables worked the way I want them to and it created a new row for each of them etc) but the other variables which I'd like copied into the other rows are only being filled into the first row. In the rows for dx2-dx30, it just has a period for the other variables.

This is the code I have:

Proc transpose data=totranspose2016 out=transposed2016 name=diagnosis prefix=code;
By uniqueadmission;
copy patientuhin cdiff primary readmit agecat died; /*want these copied to each corresponding row but only shows on first row for each
uniqueadmission number, then has a period for these variables in all the other rows*/
Var dx1-dx30; /*I want these to go from being 30 different columns to one column with one row for each dx number - this part worked*/
Run;

Is it possible to change this so the variables listed in the copy statement are copied to all of the corresponding rows?

Re: How do I get variables copied into all the new rows when transposing from wide to long?

Tom — Wed, 06 Jul 2022 18:48:30 GMT

Does UNIQUEADMISSION have a different value on every observation?

If so then just add any other variable you want kept the same as extra BY variables after it.

If you want the new variable to be named CODE instead of CODE1 (or COL1) use a RENAME= dataset option on the output dataset.

Proc transpose data=totranspose2016 out=transposed2016(rename=(code1=code))
  name=diagnosis prefix=code
;
  by uniqueadmission  patientuhin cdiff primary readmit agecat died;
  var dx1-dx30;
run;

Re: How do I get variables copied into all the new rows when transposing from wide to long?

Geoghegan — Wed, 06 Jul 2022 18:57:44 GMT

Perfect, thank you! That fixed it!