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] ??
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;
Explain further. Show us what the desired output is. In particular, how are the values of variable z derived?
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.