put date into string

[gsnidow]

Greetings all.  I'm having some trouble creating a string variable in format 'YYYY-MM-DD' given an ending date.  I would like to supply an end date, then use symput (or whatever else works) to create the begin date.  Given dt_end = '2014-02-03', I would like dt_begin to be '2014-01-27', one week earlier, but it is ending up being '19750'.  Here is what I am trying...

%let dt_end = '2014-02-03' ;

data _null_ ;

    call symput('dt_begin', cat("'",input(&dt_end, yymmdd10.)-7,"'"));

run ; quit ;

%put &dt_begin ;

I will be passing these variables into an ODBC query, so I need the single quotes on them.  I can't understand why &dt_begin is in date serial format, and not YYYY-MM-DD format.  Does anyone have any ideas?  Thank you.

Greg

[DBailey · Accepted solution]

%let dt_end = '2014-02-03' ;

data _null_ ;

     call symput('dt_begin', cat("'",put(input(&dt_end, yymmdd10.),yymmddd10.),"'"));

run ; quit ;

%put &dt_begin ;

[gsnidow]

DBailey, thank you so much.  If I'm telling the symput function that I want &dt_end to be in yymmdd10 format, why must I also tell it that I want the result to be in yymmdd10.?  That seems kind of redundant.  Anyhow, thanks again.

Greg

[Tom]

The INPUT() function is a general tool for converting strings into values.  To know what type of conversion you need to provide it an INFORMAT so that it knows what type of conversion to perform.

Dates are stored as number of days since 1/1/1960.

The PUT() function is a general tool for converting a value into a string.  To know what type of conversion you need to provide it with a FORMAT.

[gsnidow]

Thank you Tom.  I've been using SAS off and on for well over a year now, and I still find dates to be the most frustrating thing of all.

Greg

[Peter_C]

did you not notice you have to use the INPUT() function to convert your start date before subtracting 7 days?

Your result is a standard SAS-date (a number of days since 1-Jan-1960).

So you need to convert that with the PUT() function in a similar way.

Alternatively, work with SAS-date constants and convert to the DBMS format at the last stage (if you really need it even then)

SAS-date constants are a lot more understandable for us than tHat "number of days since 1960)

There are hundreds of formats an informats to handle dates.There are also two really helpful functions for date arithmetic

1. INTCK() calculates the number of date/time intervals between two dates (and/or times)
2. INTNX() returns the next date/time after +/- N intervals

WEEK is one of those intervals although your question doesn't need more than a 7-day interval, i.e. old-date+/- 7

There are aspects to interval handling you might find very useful (although you dfon't need to go there for this issue)

Your date style is very similar to the style of Teradata dates so I guess you seek the constant to use in that (dbms) kind of context.

With implicit pass-thru SQL you can just use SAS-date constants, but generating a query for the DBMS environment I guess you might need that style for dates. Here is hou you could work

%let today = %sysfunc( today(), date9. ) ;

%let dt_end = %str(%')%sysfunc( putn( "&today"d, yymmddD10.))%str(%') ;

%let dt_begin = %str(%')%sysfunc( intnx( week, "&today"d,  -1 , same),yymmddD10.)%str(%') ;