topic Re: Date format from Day to MonYy in SAS Enterprise Guide

Date format from Day to MonYy

OscarBoots1 — Sun, 20 Aug 2017 08:04:44 GMT

Hi Forum,

I would like to get all of the dates in a given month by joining 2 tables together on their 'MonYr' field.

Table A has a 'RptDate' field value of '16AUG17' format DATE7. and a 'MonYr' field derived from the RptDate field by changing the format to 'MONYY5.' so the 'MonYr' value is 'AUG17'.

Table B also has a 'RptDate' field for all Dates in 2017 and a 'MonYr' field also derived from the RptDate field by changing the format to 'MONYY5.'

When I run the below statement, I only get the result for '16AUG17' instead of all dates in AUG17.

I think this is due to my creation of the MonYr field by changing the 'RptDate' format.

Can anyone advise a solution to this?

SELECT 
RptDate 
FROM Table1 A
INNER JOIN Table2 B
ON A. MonYr = B. MonYr

Re: Date format from Day to MonYy

Jagadishkatam — Sun, 20 Aug 2017 08:47:27 GMT

I definitely agree with your reason, it is because of the monyr variable that you derived using the format monyy5. with this approach the appearnce of the variable monyr alone is changing but internally the monyr has the full numeric date.

To overcome this issue, please derive the monyr variable with put function something like below

monyr=put(rptdate,monyy5.);

this will derive the character monyr variable and on this variable if you merge you will get the expected output. Please derive the same in both the datasets you are merging.

Hope this informtion will help you.

Re: Date format from Day to MonYy

Astounding — Sun, 20 Aug 2017 13:12:37 GMT

Worth a try, but needs to be tested to see if it actually works:

on put(a.rptdate, monyy5.) = put(b.rptdate, monyy5.)

If it works, you don't need to create any new variables (not even MonYr).

In fact, it looks like you aren't selecting any variables from B. So it would probably be faster (and might work if the above doesn't work) to switch to a subquery:

Select RptDate from A

where put(RptDate, monyy5.) in

(select distinct put(RptDate, monyy5.) from B)

;

Re: Date format from Day to MonYy

OscarBoots1 — Sun, 20 Aug 2017 22:21:44 GMT

Thanks Jag, works perfectly.

Re: Date format from Day to MonYy

OscarBoots1 — Sun, 20 Aug 2017 22:22:20 GMT

Thanks Astounding, much appreciated !