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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: