3

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?

Michael A
  • 4,391
  • 8
  • 34
  • 61

1 Answers1

7

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.

Joe
  • 62,789
  • 6
  • 49
  • 67
  • This gives me an error saying "INPUT function requires a character argument." I assume I'm getting this because my `month` variable is actually a six-digit number, not a string? I tried `input(put(month), yymmn6.)` as well, but I get the same error. I'm running it with `input(put(month, yymmn6.), yymmn6.)` now to see if that works, but the job takes a while because the data set is quite large. – Michael A Apr 30 '15 at 21:35
  • 1
    If your field is a numeric field, then you have to `put` it to character first. `input(put(datefield,6.),yymmn6.)` You need the format to `put` it with in the `put` function. Your second one doesn't work because that's acting as if your data were already an underlying date field - so `201403` would be something like 400 years in the future! – Joe Apr 30 '15 at 21:43
  • Ah, ok. That works. If I specify the input format as `MMDDYY10.`, will that automatically set the day as the first day of the month (which I want), or do I need to do more to expand it to a higher frequency? (I realize that's a somewhat separate question, but I realized I need to do that so I wanted to wrap up that one detail). – Michael A Apr 30 '15 at 21:50
  • The day will always be the first day of the month when using `YYMMNw.` informat. You can specify any format for the resulting field you wish. – Joe Apr 30 '15 at 21:55
  • Any benefit to this approach: `MDY(MOD(x,100),1,INT(x/100))`? – mjsqu May 01 '15 at 05:28