BookmarkSubscribeRSS Feed
sagrim
Calcite | Level 5

Hello all.

I am attempting to join 2 datasets in JMP using a unique identifier plus a closest date match. Rather than having dates in MDYHMS format, I have it converted to Unix timestamps (the UNIX date is the number of seconds since midnight, January 1, 1970.) The data only spans 2012- 2015 so there shouldn't be any issue with using the timestamp in lieu of the DMYHMS format. I am not particularly attached to the timestamp format though so if it seems better to do the MDYHMS format then I am flexible on that. The timestamp/date from t1 should always be greater than or equal to the timestamp/date from t2. One unique identifier will have many date matches so I want it to FIRST match on "EMP_ID_UID" and THEN match up the closest timestamps in the "date" variables. I am basing some code off of a closed topic here:

https://communities.sas.com/t5/SAS-Data-Management/Merging-two-tables-by-choosing-the-CLOSEST-dates/...

SELECT *
FROM "V1.8" t1
LEFT OUTER JOIN "Subset of Relevant Unit Org Codes ONLY" t2
ON t1.EMP_ID_UID = t2.EMP_ID_UID AND t1.date>=t2.date
WHERE t1.date >= t2.date
GROUP BY t1.EMP_ID_UID,t1.date
HAVING abs(t1.date-t2.date)=min(abs(t1.date-t2.date))
ORDER BY t1.EMP_ID_UID
;
quit;

The query begins to run without any error messages (I understand that doesn't necessarily mean the code is correct for what I want it to do) but then it just sort of hangs there indefinitely without completing the join.

Any thoughts or assistance would be greatly appreciated. Thank you !

1 REPLY 1
PGStats
Opal | Level 21

No need to repeat the join condition in the where clause. Try on a small table first to see if the problem is resources related.

 

JMP has their own community: https://community.jmp.com/ 

 

It is best to post your question there. Best of luck!

PG

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 1 reply
  • 1680 views
  • 0 likes
  • 2 in conversation