BookmarkSubscribeRSS Feed
paulrockliffe
Obsidian | Level 7

I'm building some data tables that include data across several years.  The last step is to transpose the rows so that I have a column for each year.  I'm not using the transpose function for this, I'm using a program to create 5 tables then joining these back together in Query Builder.

 

Each row has a column with the year in it, an entity identifier and a total score.

 

The years that are included are the previous five years, this is controlled automatically by filters in the process flow that take account of the current date, so on the first of January the data will drop a year of the end and add the previous year to the analysis.

 

I have got it setup with column names YM1 to YM5, ie current Year Minus 1 to 5 to identify the years.  But this isn't perfect because I then have to add an explanation of what that means when the results are sent out and people generally don't like it.  

 

Is it possible to use a program to change column names to add the year?  I've added a table below that should explain what I mean properly I hope.

 

Thanks!

 

Initial Table:

 

Name       || Year  || Total

--------------------------------

Company || 2016 || 567

Company || 2015 || 684

Company || 2014 || 123

Company || 2013 || 678

Company || 2012 || 348

 

Is transposed into:

 

Name       ||  2016 Total  ||  2015 Total  ||  2014 Total  ||  2013 Total  ||  2012 Total

-----------------------------------------------------------------------------------------------------------

Company ||      567         ||      684         ||      123        ||      678        ||      348

 

3 REPLIES 3
Kurt_Bremser
Super User

This is automatically handled by proc transpose:

data have;
input company $ year total;
cards;
Company 2016 567
Company 2015 684
Company 2014 123
Company 2013 678
Company 2012 348
;
run;

proc transpose
  data=have
  out=want (drop=_name_)
  prefix=total_
;
by company;
id year;
var total;
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post test data in the form of a datastep.

 

As such this hasn't been tested:

data inter;
  set have;
  length vlab $100;
  vlab=catx(' ',put(year,4.),"Total");
run;

proc transpose data=inter out=want;
  by name;
  var total;
  id year;
  idlabel vlab;
run;

Your column names will be _2016 _2015 of course as 2016 is not a valid SAS name, but this will give you the label you want.

Reeza
Super User

Use a REPORT procedure to present the data like that, but ideally you keep it stored in a long format. You have more flexibility with the data in the long run that way.

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
  • 3 replies
  • 725 views
  • 0 likes
  • 4 in conversation