PROC SQL Concat numbers in SAS and format as date - SAS E Guide 5.1

Question

Good morning all,

I've got 3 different columns in a data set that represent a month, a date, and a year as numbers. My issue right now is concatenating these together in PROC SQL while keeping them formatted as a date. So far, I've tried the following, but I'm only getting results that show every date as a period ("."). You'll notice that I had to convert them to characters to be able to concatenate them.

PROC SQL;
    SELECT
        INPUT(PUT(f.MTH,z2.) || '-' || PUT(f.DAY,z2.) || '-' || PUT(f.YR,z4.),date9.)
    FROM
        table f
;QUIT;

I tired rearranging the year/day/month, and tried with and without the '-' between them. Still, I'm just getting a period in every row.

It is worth noting that the numbers look fine when just concatenated by themselves, without any attempt at date formatting. But I need their column to be a DATE column for the process the data is being used for.

PUT(f.YR,z4.)|| PUT(f.MTH,z2.) ||PUT(f.DAY,z2.)

^looks fine with and without '-' separating the numbers. On that note, the date9. format isn't the absolute needed date format, I really just need it to be a DATE of some sort.

What am I missing here? Should I not be relying so heavily on PROC SQL to do this?

Answer 1

Use the MDY function, since the variables are already numbers, I think it's the best option.

proc sql;
select 
    mdy(12,1,2015) format ddmmyy10. as DAY_DATE_FORMAT
from table;
quit;

Just to explain to you why your code didn't work.

The date9. informat you're using expects a month entry like "Jan", "Feb", etc. But you're passing a number to the informat (z2.). If you change date9. to ddmmyy10 in your input, it will work.