BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
OscarBoots1
Quartz | Level 8

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 
1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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. 

Thanks,
Jag

View solution in original post

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

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. 

Thanks,
Jag
OscarBoots1
Quartz | Level 8

Thanks Jag, works perfectly.

Astounding
PROC Star

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)

;

OscarBoots1
Quartz | Level 8

Thanks Astounding, much appreciated !

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3081 views
  • 0 likes
  • 3 in conversation