Programming Column Names?

[paulrockliffe]

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

[Kurt_Bremser]

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]

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]

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.