How to use proc transpose on a 2 by 2 matrix

[eagles_dare13]

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

[art297 · Accepted solution]

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;

[naveen_srini]

If using a datastep:

data want;

set have;

Name=vname(abc);

Age=abc;

drop abc;

run;

[Reeza]

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

[Tom]

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;