How do I create this strange and exotic format ---> DDMMMYYYY:HH:MM:SS

[davidvalentine]

Hello,

I am looking for help converting my date into the following strange and exotic format: DDMMMYYYY:HH:MM:SS for use in a WHERE clause.  First things first: my SAS version is 7.15 32-bit.  The code I have is below:

*Date macro is below;

%Let End_of_lmonth = %sysfunc(intnx(Month,%sysfunc(today()),-2,end),Date11.);

%Put Check to see if this is the end of the last two months.  If not, fix it.  &End_of_lmonth;

PROC SQL inobs=1000;

  select * from work.accnt

  where accnt.snpsht_dt = '&end_of_lmonth';

QUIT;

Although this runs, it returns no rows.  The End_of_lmonth variable I created resolves to '30-SEP-2019', but when I replace the &end_of_lmonth variable in the Where clause with '30-SEP-2019', the query runs and pulls in data.  In other words, the query below works just fine.

PROC SQL inobs=1000;

  select * from work.accnt

  where accnt.snpsht_dt = '30-SEP-2019';

QUIT;

When I examined the results further, this works because column holding the data comes out to a format like this: 30SEP2019:00:00:00 instead of 30-SEP-2019.  So how can I create my macro variable to be in the strange and exotic format that works?  Any help is truly appreciated!!!

[ballardw]

Is your accnt.snpsht_dt a character variable? If it is character then you likely need to manipulate both your variable AND the date.

If not then it should be a datetime since you show DDMMMYYYY:HH:MM:SS and I don't see any attempt to use any time portion.

If the value is a datetime value then you likely should not format the macro value (why a macro at all?)

 where datepart( accnt.snpsht_dt) = intnx('month',today(),-2,'E');

If the value is in a different data base then you may need to mention that.

[davidvalentine]

Hi ballardw!

Accnt.snpsht_dt is a date variable. I am using a macro because I left the other portion of code that references both the beginning and end of the month in several Where clauses out for the sake of brevity, so I figured it’ll be easier to just create two macro variables that give me the beginning and end of the month and just reference those two variables several times.

As far as the value being in a different data base, my Proc SQL is connecting to an Oracle database if that’s what you mean. I’ll give this a go and reply back if this works. Thank you, ballardw!

[Tom · Accepted solution]

You seem a little confused about how dates and times work in SAS.  SAS stores all of them as numbers. SAS only has two variable types, floating point numbers and fixed length character strings.

Date values, like the output of the TODAY() function, are the number of days since 1960. Time and Datetime values are the number of seconds. Time is from midnight and Datetime is from 1960.

The style of displaying datetime values you show is the default format, DATETIME,  that SAS uses to for the display of datetime values. Nothing exotic about it.

If you want to create a datetime value using INTNX() function you need to use a datetime interval and datetime starting value.

%let start=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-2,e),datetime20.);

Result:

1491  %put &=start;
START=30SEP2019:23:59:59

If you want to use that string to actually compare to another datetime value then you need to convert it to a datetime literal.

where accnt.snpsht_dt between "&start"dt and "&end"dt

If you are not using the macro variable to create text that humans need to read you can just leave the value as the raw number of seconds instead of formatting it (converting it to text) with the DATETIME format.  Then the code is a little easier to write.

So you might use this to find datetime values in the previous month.

%let start=%sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,b));
%let end=%sysfunc(intnx(dtmonth,&start,0,e));
...
where accnt.snpsht_dt between &start and &end 

[davidvalentine]

Hi Tom,

This is really helpful information; I'll give your solutions a go and let you know if they do the trick.  Thanks!

[davidvalentine]

Hello Tom,

I gave your advice a go but I received an error I never saw before.  It is below :

• ERROR: ORACLE prepare error: ORA-00920: invalid relational operator.

This is in regards to using all of the suggested solutions that were presented (where accnt.snpsht_dt between "&start"dt and "&end"dt and where accnt.snpsht_dt between &start and &end). 

What would you suggest I do next?

[Tom]

---

@davidvalentine wrote:

Hello Tom,

I gave your advice a go but I received an error I never saw before.  It is below :

• ERROR: ORACLE prepare error: ORA-00920: invalid relational operator.

This is in regards to using all of the suggested solutions that were presented (where accnt.snpsht_dt between "&start"dt and "&end"dt and where accnt.snpsht_dt between &start and &end). 

What would you suggest I do next?

---

Show what code you actually gave to Oracle that caused it to produce such a message. Note you cannot use SAS syntax in code you are passing directly to Oracle to use.  If you let SAS convert the code into SQL for you then turn on logging and see what code it generated.

[davidvalentine]

I would share the Oracle code in SAS but it's, eh, "sensitive" and it would take waaaaay too long for me to edit it to just show the barebones code (several hundred lines at least, hence the macros to replace these crazy dates!).

Also, I've troubleshooted this in further detail, and it turns out that the end of month variable is actually resolving like so: 30SEP2019:00:00:00 instead of what we thought it would be: 30SEP2019:23:59:59.  What would I do to take the 23:59:59 and make it 00:00:00?

In the meantime, I'm going to mark your original proposed solution as an answer since it gave me the most guidance and aid.

[Tom]

You could use another INTNX() to move the start of the day.

intnx('dtday',intnx('dtmonth',XXX,-1,'e'),0,'b')

Or if you are just generating strings you could just generate the date part and add the zero time part as constant text.

%let end=%sysfunc(intnx(month,%sysfunc(today()),-1,e),date9.):00:00:00;

[Astounding]

If I hear you right:

• The macro variable resolves properly, and
• The hard-coded version produces the correct results.

In that case, all you need to change is this:

'&end_of_lmonth'

Single quotes suppress all macro activity so &END_OF_LMONTH is not resolving in  your query.  Just use double quotes:

"&end_of_lmonth"

[davidvalentine]

Hi Astounding,

I never knew this about the single vs. double quotes; I'll definitely try this.  Thanks!