oracle sql code to sas proc sql - add months

0
votes

I am rewriting code from oracle sql to sas proc sql and I have problem with this:

where demandes_fin.per_idt = to_char(add_months(to_date(fin.per_idt,'yyyymm'),1),'yyyymm'))

demandes_fin.per_idt and fin.per_idt are INT variables - for example 201612.

functions to_char, add_months, to_date are not working in SAS proc sql. I have tried to replace them using put, input, intnx, format but it didn't work as I expected.

I have tried to put this code in select statement to see values, that was generated: 

   intnx('month', input(put(fin.per_idt,6.),yymmn6.), 1) as dt1

fin.per_idt was 201701 and generated value was 20851.

Do you have any ideas how to code it?

Thank you so much.

1
sqlsql-serveroraclesas

1 Answers

0
votes

Looks like you have already solved your problem. SAS stores dates as the number of days since 01JAN1960. The number 20,851 represents 01FEB2017 which is one month after January 2017.

If you want to keep your result as a SAS date value just add the FORMAT keyword to your definition so that the date will display in a format that humans can understand.

intnx('month', input(put(fin.per_idt,6.),yymmn6.), 1) as dt1 format date9.

If you want to convert it back into the strange integer value you started with then just add some more PUT() and INPUT() function calls.

input(put(intnx('month', input(put(fin.per_idt,6.),yymmn6.), 1),yymmn6.),6.)