topic Re: Convert a Column Title to Date Format in New SAS User

Convert a Column Title to Date Format

Hugo1989 — Wed, 13 Nov 2019 12:32:29 GMT

I am currently writing a piece of code to extract specific elements of raw data into a new file. My raw data file imports from an Excel.XLSX file and has 36 columns which are titled as dates input as "dd/mm/yyyy".

When I bring this data into SAS using Proc Import it converts the format of the column titles with the following stated within the log:

e.g. Variable Name Change. 42370 -> _42370

What I now need is to change the column titles back to a date format as they are showing in my data extract in the above format, e.g. "_ddddd". I need a piece of code to identify where columns have a title such as the one above or similar (I have 36 columns each with a unique header according to which month it is) and then takes that and converts it to a date value.

Any help would be really appreciated.

Re: Convert a Column Title to Date Format

PaigeMiller — Wed, 13 Nov 2019 12:54:31 GMT

@Hugo1989 wrote:

What I now need is to change the column titles back to a date format

SAS variable names cannot be dates, so I am not sure what you are wanting as the desired end result.

Re: Convert a Column Title to Date Format

Hugo1989 — Wed, 13 Nov 2019 13:23:14 GMT

As I say the raw data has the columns named as 'dd/mm/yyyy' but are formatted to display as 'mmm-yy', e.g. the current month is displayed as 'Nov-19'.

What I would like to do is reformat my SAS output so that I can get it back to the format in the raw data, whether that be 'dd/mm/yyyy' or 'mmm-yy' I'm not precious about.

Re: Convert a Column Title to Date Format

Kurt_Bremser — Wed, 13 Nov 2019 13:23:39 GMT

First, you need to correct your data layout and transpose from wide to long (Maxims 33 & 19).

Then you extract substr(_name_,2) and convert it to a number. You add '30dec1899'd, attach a date format, and you're done.

Re: Convert a Column Title to Date Format

Kurt_Bremser — Wed, 13 Nov 2019 13:28:25 GMT

@Hugo1989 wrote:

As I say the raw data has the columns named as 'dd/mm/yyyy' but are formatted to display as 'mmm-yy', e.g. the current month is displayed as 'Nov-19'.

What I would like to do is reformat my SAS output so that I can get it back to the format in the raw data, whether that be 'dd/mm/yyyy' or 'mmm-yy' I'm not precious about.

Don' perpetuate the layout mistakes frequently committed in Excel, which is not designed for mass data processing, but presenting small-scale data to humans.

Free yourself from "spreadsheet thinking", and think SAS (which works more like a database) instead (Maxim 17).

Re: Convert a Column Title to Date Format

Tom — Wed, 13 Nov 2019 14:52:49 GMT

You probably want to make the output as a REPORT instead of a dataset.

So let's mimic your input gibberish.

data have ;
  input id :$10. _42370 _42401;
cards;
NJ 345  456
NY 1234 789
;
proc print data=have; run;

Obs    id    _42370    _42401

 1     NJ      345       456
 2     NY     1234       789

First convert it to a TALL structure where the column header is now in a variable that you can manipulate.

proc transpose data=have out=tall ;
  by id ;
  var _: ;
run;

Now you can convert those goofy numbers into actual DATE values.

data tall2;
  set tall ;
  date = input(substr(_name_,2),32.)+'30DEC1899'd;
  format date date9. ;
run;
proc print data= tall2;
run;

Obs    id    _NAME_    COL1         date

 1     NJ    _42370     345    01JAN2016
 2     NJ    _42401     456    01FEB2016
 3     NY    _42370    1234    01JAN2016
 4     NY    _42401     789    01FEB2016

Now you can make a REPORT that uses the value of the date as the column header.

proc report data=tall2 ;
  column id col1,date ;
  define id / group ;
  define date / across order=internal format=monyy7. ' ';
  define col1 / sum ' ';
run;

  id            JAN2016    FEB2016
  NJ                345        456
  NY               1234        789

Re: Convert a Column Title to Date Format

Hugo1989 — Thu, 14 Nov 2019 08:59:17 GMT

Really helpful Tom, thanks very much!!

Re: Convert a Column Title to Date Format

Kurt_Bremser — Thu, 14 Nov 2019 12:50:33 GMT

Note how the transposition I was talking about (and which is the most important step in @Tom's solution) immediately opens you up to make use of the powers of SAS.