DATA Step, Macro, Functions and more

put date into string

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 124
Accepted Solution

put date into string

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


Accepted Solutions
Solution
‎02-03-2014 12:29 PM
Super Contributor
Posts: 578

Re: put date into string

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

data _null_ ;

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

run ; quit ;

%put &dt_begin ;

View solution in original post


All Replies
Solution
‎02-03-2014 12:29 PM
Super Contributor
Posts: 578

Re: put date into string

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

data _null_ ;

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

run ; quit ;

%put &dt_begin ;

Frequent Contributor
Posts: 124

Re: put date into string

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

Super User
Super User
Posts: 7,039

Re: put date into string

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.

Frequent Contributor
Posts: 124

Re: put date into string

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

Valued Guide
Posts: 2,177

Re: put date into string

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) Smiley Wink

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(%') ;

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 553 views
  • 6 likes
  • 4 in conversation