2

In test_1 table, the my_date field is a "DATE9." format.
I would like to convert it to a pure numeric format (number length 8) which is of the form YYYYMMDD.
I would also like to do this in a proc sql statement ideally.

Here's what I have so far.
Clearly I need something to manipulate the my_date field.

rsubmit;
proc sql;
    CREATE TABLE test_2 AS      
    SELECT
        my_date
    FROM
        test_1
    ;
quit;    
endrsubmit;

FYI: I am finding it quite difficult to understand the various methods in SAS.

To clarify, the field should actually be a number, not a character field, nor a date.

George Kagan
  • 5,913
  • 8
  • 46
  • 50
cammil
  • 9,499
  • 15
  • 55
  • 89
  • What do you mean by 'convert'? You just want to make it look like `20141231`? Or you want it to be the number `20141231` in actuality (which is not a date as far as SAS understands it)? – Joe Aug 05 '14 at 19:08
  • yep, i need it to be an actual number. ive edited the question to make more clear. – cammil Aug 05 '14 at 19:13

2 Answers2

11

If you want the field to store the value 20141231 for 31DEC2014, you can do this:

proc sql;
  create table want as
   select input(put(date,yymmddn8.),8.) as date_num
     from have;
quit;

input(..) turns something into a number, put(..) turns something into a string. In this case, we first put it with your desired format (yymmddn8. is YYYYMMDD with no separator), and then input it with 8., which is the length of the string we are reading in.

In general, this should not be done; storing dates as numerics of their string representation is a very bad idea. Try to stay within the date formats, as they are much easier to work with once you learn them, and SAS will happily work with other databases to use their date types as well. If you want the "20141231" representation (to put it to a text file, for example), make it a character variable.

Joe
  • 62,789
  • 6
  • 49
  • 67
-4

Don't.

You lose the ability to use built in SAS functions for date calculations.

SAS stores dates as numbers, 0 being Jan 1, 1960 and increments from there. Formats are used to display the formats as desired for reporting and presentation.

Reeza
  • 20,510
  • 4
  • 21
  • 38
  • This is a comment, not an answer. That sort of thing is okay if included with an actual answer (see: my answer), but if it's just telling the user not to do [x thing user asks about], don't provide it as an answer. – Joe Aug 05 '14 at 22:07
  • Agree with Joe here. I did not want to go into why I need it because that would have cluttered my question and made it difficult for others to understand. – cammil Aug 06 '14 at 07:22
  • Fair enough. What's the protocol here, let the answer be down voted, or delete (if that's even possible)? – Reeza Aug 06 '14 at 21:00