Help using Base SAS procedures

How to use proc transpose on a 2 by 2 matrix

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

How to use proc transpose on a 2 by 2 matrix

I have this data in an excel: 2 rows and 2 columns...

Name  abc
Age   14


Nameabc
Age14

I want to represent it like this: 

Name Age
abc  14

NameAge
abc14

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


Accepted Solutions
Solution
‎12-31-2014 01:11 PM
PROC Star
Posts: 7,363

Re: How to use proc transpose on a 2 by 2 matrix

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=_Smiley Happy;

  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_)(Smiley Happy;

run;

proc import datafile="c:\temp\toimport.csv" out=want

  replace dbms=csv;

run;

View solution in original post


All Replies
Solution
‎12-31-2014 01:11 PM
PROC Star
Posts: 7,363

Re: How to use proc transpose on a 2 by 2 matrix

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=_Smiley Happy;

  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_)(Smiley Happy;

run;

proc import datafile="c:\temp\toimport.csv" out=want

  replace dbms=csv;

run;

Frequent Contributor
Posts: 115

Re: How to use proc transpose on a 2 by 2 matrix

If using a datastep:

data want;

set have;

Name=vname(abc);

Age=abc;

drop abc;

run;

Super User
Posts: 17,836

Re: How to use proc transpose on a 2 by 2 matrix

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

Super User
Super User
Posts: 6,500

Re: How to use proc transpose on a 2 by 2 matrix

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 345 views
  • 1 like
  • 5 in conversation