- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
sorry 201211 would be november 2012.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here is the equivalent SAS code:
recalldate1 = put( intnx( 'month', recalldate, recall, 's' ), yymmn6. );
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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. );
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;