BookmarkSubscribeRSS Feed
Scott86
Obsidian | Level 7

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

8 REPLIES 8
Kurt_Bremser
Super User

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
Scott86
Obsidian | Level 7

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

Patrick
Opal | Level 21

@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

Kurt_Bremser
Super User

@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.

Patrick
Opal | Level 21

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Tom
Super User Tom
Super User

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;

 

Scott86
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2203 views
  • 0 likes
  • 5 in conversation