Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

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

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!


Accepted Solutions
Solution
‎04-18-2012 06:22 PM
Contributor
Posts: 41

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

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;

View solution in original post


All Replies
Contributor
Posts: 41

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

sorry 201211 would be november 2012.

Frequent Contributor
Posts: 101

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

Here is the equivalent SAS code:

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

Contributor
Posts: 41

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

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)Smiley SadColumn).
      2907:47
NOTE: Numeric values have been converted to character values at the places given by:
      (Line)Smiley SadColumn).
      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?

Frequent Contributor
Posts: 101

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

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

Contributor
Posts: 43

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

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.

Solution
‎04-18-2012 06:22 PM
Contributor
Posts: 41

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 5568 views
  • 6 likes
  • 3 in conversation