How to convert date in SAS to YYYYMMDD number format

Question

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.

Solution

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.

Comments

[Joe]

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.

[cammil]

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.

[Reeza]

Fair enough. What's the protocol here, let the answer be down voted, or delete (if that's even possible)?