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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.