I have this data in an excel: 2 rows and 2 columns...
Name abc
Age 14
Name | abc |
---|---|
Age | 14 |
I want to represent it like this:
Name Age
abc 14
Name | Age |
---|---|
abc | 14 |
How can i use proc transpose to do this? I am looking for a method that would work even if Name / Age variables are null in the input
There is probably a more direct way, but at least the following will accomplish the task:
proc import datafile="c:\temp\have.xlsx" out=have
replace dbms=excel;
sheet=sheet1;
getnames=no;
run;
proc transpose data=have out=to_export (drop=_:);
var f1 f2;
run;
filename toimport 'c:\temp\toimport.csv' lrecl=256;
data _null_;
set to_export;
file toimport dsd delimiter=',' termstr=crlf;
put (_all_)(:);
run;
proc import datafile="c:\temp\toimport.csv" out=want
replace dbms=csv;
run;
There is probably a more direct way, but at least the following will accomplish the task:
proc import datafile="c:\temp\have.xlsx" out=have
replace dbms=excel;
sheet=sheet1;
getnames=no;
run;
proc transpose data=have out=to_export (drop=_:);
var f1 f2;
run;
filename toimport 'c:\temp\toimport.csv' lrecl=256;
data _null_;
set to_export;
file toimport dsd delimiter=',' termstr=crlf;
put (_all_)(:);
run;
proc import datafile="c:\temp\toimport.csv" out=want
replace dbms=csv;
run;
If using a datastep:
data want;
set have;
Name=vname(abc);
Age=abc;
drop abc;
run;
I don't know how big your excel file is but there's an option to transpose in Excel as well - copy>paste special>Transpose
You can do it with PROC TRANSPOSE, but you do need to know the names of the two columns in the original table.
Here is your example with the input Excel file replaced with a data step so that others can play with the code.
data have ;
input Name $ abc ;
cards ;
Age 14
;;;;
proc transpose data=have out=want name=Name ;
id name;
var abc ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.