topic Re: how can i get to_char to work in proc sql without connecting to oracle? in SAS Procedures

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

teg_76 — Wed, 18 Apr 2012 20:49:30 GMT

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!

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

teg_76 — Wed, 18 Apr 2012 20:50:27 GMT

sorry 201211 would be november 2012.

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

FloydNevseta — Wed, 18 Apr 2012 21:09:22 GMT

Here is the equivalent SAS code:

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

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

teg_76 — Wed, 18 Apr 2012 21:16:27 GMT

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?

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

FloydNevseta — Wed, 18 Apr 2012 21:32:17 GMT

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. );

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

BobD — Wed, 18 Apr 2012 21:41:20 GMT

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.

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

teg_76 — Wed, 18 Apr 2012 22:22:27 GMT

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;