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 !
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.