how can i get to_char to work in proc sql without connecting to oracle?

[teg_76]

I'm having more date issues.  I need to add a certain amount of months to a date, and the convert it to the 'YYYYMM' format.

I can do this in PROC SQL if I'm pulling data from the oracle db.  Once I have a work table in SAS though I can no longer do this.  The proc sql doesn't recognize the to_char function or the add_months function. I don't know how to do the equivalent in SAS.

Basically, if I have a date

16SEP2012:00:00:00

I need to add months to it, than convert it to a 'YYYYMM' format.  So in my code below, if RECALL=2,

then I would add two months to the date above, and then convert it to YYYYMM.  I'd end up with 201211 (december 2011).

Here's what I'm doing:

PROC SQL;

UPDATE PL.TREATMENT_PLANS_ADJ_RECALL SET RECALLDATE1=TO_CHAR(ADD_MONTHS(R.RECALLDATE, RECALL),'YYYYMM');

RUN;

If I were connecting to Oracle it would work.  But I can't connect to Oracle because that's not where my table is.

Thanks for helping out this SAS date newbie!

[teg_76]

sorry 201211 would be november 2012.

[FloydNevseta]

Here is the equivalent SAS code:

recalldate1 = put( intnx( 'month', recalldate, recall, 's' ), yymmn6. );

[teg_76]

Hi SAS_Bigot,

Thanks for your help.  I'm getting an error when I run this:

DATA PL.TREATMENT_PLANS_ADJ_RECALL_2;
     SET PL.TREATMENT_PLANS_ADJ_RECALL;
     recalldate2 = intnx( 'month', recalldate, recall, 's' );
RUN;

I started with the add months portion of the code.  Here's the error I get:

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      2907:47
NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      2907:19
NOTE: Invalid argument to function INTNX at line 2907 column 19.
RECALL=6 RECALLTYPE=PX RECALLDATE=16SEP2012:00:00:00 RECALLDATE1=201209 RECALLDATE2=.
RECALLDATE3=201309 RECALLDATE4=201403 RECALLDATE5=201409 UNITS=3 CARIES=C0-L PERIO=P1-L AGE=3
_ERROR_=1 _N_=1.....................

Do you know what may be the problem?

[FloydNevseta]

Looks like recalldate is a character. Read it in with a datetime. informat and get the date part of the variable. Revised code:

recalldate2 = put( intnx( 'month', datepart( input( recalldate, datetime. )), recall, 's' ), yymmn6. );

[BobD]

It probably means that RECALLDATE in your SAS dataset is a character variable.  It might just look like a time-stamp, but I bet it's just a character string.

Run a PROC CONTENTS on your dataset to be sure.  If it is in fact numeric, it is a time-stamp (with a datetime format), and you will need to extract the "date" portion:

recalldate2 = intnx( 'month', datepart(recalldate), recall, 's' );

On the other hand, if it is a character string, re-read the first nine characters as a date:

recalldate2 = intnx( 'month', input(substr(recalldate,1,9),date9,), recall, 's' );

That should do it.

[teg_76 · Accepted solution]

Thanks SAS Bigot and BobD for saving me hours of frustration!!!!!  Wow my day just got better.  It worked.  Here's the final solution:

DATA PL.TREATMENT_PLANS_ADJ_RECALL;

SET PL.TREATMENT_PLANS_ADJ_RECALL_SETUP;

recalldate2 = put(intnx( 'month', datepart(recalldate), recall, 's' ), yymmn6. );

RUN;