Desktop productivity for business analysts and programmers

Proc transpose by two columns

Reply
Contributor
Posts: 29

Proc transpose by two columns

Hi Everyone,

 

I have a massive data set and I'd like to transpose two columns and have the values correspond to those new columns.

 

I've attached a dummy dataset. I'd like to transpose by fin and fin2 and have the correct val and val2 correspond to the correct column. For example val2 does not relate to fin so I just want it to populate under fins columns and vice versa for fin and val. Please view attached excel. It should go to one row. Please view attached to see example.

 

I appreciate any help

 

Regards

Super User
Posts: 9,914

Re: Proc transpose by two columns

Please post example data in a reliably reusable way, meaning in a data step with datalines (see my footnotes). Excel spreadsheets are mostly unusable, as they

  • can't hold column attributes (lengths, formats)
  • pose a SEVERE security risk (after all, Office files are the main medium for malware on the internet)
  • are therefore not accessible to many of us who work professionally from inside a corporate network
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 29

Re: Proc transpose by two columns

Posted in reply to KurtBremser

Sorry I have not figure out how to paste tables into posts yet.

Respected Advisor
Posts: 4,679

Re: Proc transpose by two columns


@Scott86 wrote:

Sorry I have not figure out how to paste tables into posts yet.


Don't post tables, post SAS code creating the sample data so we can help you without having first to do prep work for you.

Capture.JPG

Super User
Posts: 9,914

Re: Proc transpose by two columns


@Scott86 wrote:

Sorry I have not figure out how to paste tables into posts yet.


You just have to read my footnotes and act on the advice contained there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,679

Re: Proc transpose by two columns

@Scott86

Macro and sample code here:

https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

 

One of the samples provided will likely be close to what you need.

Super User
Super User
Posts: 9,427

Re: Proc transpose by two columns

Arrays are probably the way forward.  Not opening an Excel file, but something like:

data want;
  set have;
  array fin{2} $20;
  retain fin1 fin2;
  by id1;
  if first.id1 then call missing(of fin{*});
    if upid=1 then fin{1}=fin;
    else fin{2}=fin;
  if last.id1 then output;
run;

Am obviously totally guessing on the data here as can't see it.  But I assume you have an id1 variable (or something similar which is the id for the transpose), and upid variable which indicates wether it should be in the first or second column.

Super User
Super User
Posts: 7,935

Re: Proc transpose by two columns

[ Edited ]

Why was it easier to create and upload an XLSX file than to type the same information into the forum?

 

Any way it looks like your problem is that you have two id (name) columns and two value columns.

data have ;
  input group $ name1 $ value1 name2 $ value2 ;
cards;
A VAR1 100 VAR2 200
A VAR3 300 VAR4 400
;

data want;
  input group $ var1 var2 var3 var4 ;
cards;
A 100 200 300 400
;

I would suggest just combining the two pairs into one pair and transposing that. 

You could use a data step view to avoid duplicating the data first if you are worried it is too large.

data both / view=both ;
  set have (keep=group name1 value1 )
      have (keep=group name2 value2 rename=(name2=name1 value2=value1))
  ;
  by group;
run;

proc transpose data=both out=want ;
  by group;
  id name1;
  var value1;
run;

 

Contributor
Posts: 29

Re: Proc transpose by two columns

Thanks everyone,

 

I ended up just splitting the datasets in two then transposing each of them and then merging them all back together. The key was using the ID statement to put the inputs into the column titles and having the values correspond to the correct column name.

 

Thanks again

Ask a Question
Discussion stats
  • 8 replies
  • 181 views
  • 0 likes
  • 5 in conversation