topic Re: PROC Transpose force column names in SAS Programming

PROC Transpose force column names

CSK_ROCKS — Wed, 01 Dec 2021 19:46:11 GMT

I have a dataset with these values. I expect values for val as "Z" in future. But currently i have values for x/y.

ID Val

1 x

2 x

3 x

4 y

5 y

6 y

when i transpose it using proc transpose, i get the column names as ID ,[x],[y]

Is there any way i can force the column names as ID ,[x],[y],[z] ??

Re: PROC Transpose force column names

PaigeMiller — Wed, 01 Dec 2021 20:03:22 GMT

Explain further. Show us what the desired output is. In particular, how are the values of variable z derived?

Re: PROC Transpose force column names

Tom — Wed, 01 Dec 2021 20:46:38 GMT

You example does not look very useful. Let's make another.

data have;
   input row column name $ value ;
cards;
1 1 X 100
1 2 Y 200
2 1 X 300
2 2 Y 400
3 1 Y 500
4 1 X 600
;
proc transpose data=have out=want(drop=_name_);
  by row;
  id name;
  var value;
run;

So you get the results:

Obs    row     X      Y

 1      1     100    200
 2      2     300    400
 3      3       .    500
 4      4     600      .

You could force the extra variables in a separate step.

data fixed;
  length row x y z 8;
  set want;
run;

Or you could introduce an extra set of observations into the input data fed to PROC TRANSPOSE. (note this step could be a VIEW instead of physical copy of the data).

data for_transpose;
   set have;
   if _n_=1 then do name='X','Y','Z';
       row=.;
       output;
   end;
  set have;
  output;
run;

Then drop that ROW=. observation on the output.

proc transpose data=for_transpose out=want(drop=_name_ where=(not missing(row)));
  by row;
  id name;
  var value;
run;