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.

Answer 1

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.