How do I convert a number column like 200012 to a SAS date variable using PROC SQL?

Question

I have a SAS data set with a text field customer_id and a numeric field month in the format YYYYMM. I'm creating a view of these data, and I want to convert the data to a standard SAS date that will (hopefully) be preserved on export. For example:

proc sql;
    create view my_view as
        select customer_id, month from raw_dataset;
    quit;

proc export data = my_view
    file = "out.dta"
    dbms = stata replace;
quit;

Looking at the date documentation, it looks like the number is in the form (although not the data type) YYMMN., but I want it in a format that SAS can work with as a date, not just a number e.g. with proc expand.

I've seen a lot of questions using combinations of put and datepart, but since I don't want the variable as a string and don't already have a datetime variable, I'm not sure how to apply those.

How do I convert this column to a SAS date data type when I run this SQL query?

Joe Joe · Accepted Answer · 2015-04-30T21:21:04

YYMMN. is the right informat to use, and input is exactly how you get it there so it's a date. Try it!

data want;
  x='201403';
  y=input(x,YYMMN6.);
  put _all_;
run;

Of course, y now probably should be formatted nicely if you want to look at it, but it doesn't need to be.

In PROC SQL this works just the same way.

proc sql;
  create view want_v as
    select x, y, input(x,yymmn6.) as z format=date9.
        from want;
quit;

This also adds a format so it looks readable, but it's still a date variable underneath identical to y.

How do I convert a number column like 200012 to a SAS date variable using PROC SQL?

1 Answers