When I run the code below, I get the log Note "The execution of this query involves performing one or more Cartesian product joins that can not be optimized." on the LEFT JOIN code highlighted in Bold. Is there a better way to Join the dstDates and Sales tables? My goal is to use the Daylight Savings Time when applicable. The salesDate is in UTC. data dstDates; format date1 datetime18.0 date2 datetime18.0; input date1 datetime18.0 date2 datetime18.0 ; cards; 10MAR2019:02:00:00 03NOV2019:02:00:00 ; data sales; format localStandard datetime18.0 localDaylight datetime18.0; input salesDate datetime18.0 ; localStandard = tzoneu2s(salesDate,'EST'); localDaylight = tzoneu2s(salesDate, 'EDT'); cards; 08MAR2019:02:00:00 12MAR2019:02:00:00 ; proc print data=sales; title "Sales"; format salesDate datetime18.0; PROC SQL; CREATE TABLE work.final1 as SELECT sales.salesDate, localStandard, localDaylight, CASE WHEN date1 IS NOT NULL then localDaylight else localStandard END AS newDate format datetime18.0 FROM sales sales LEFT JOIN dstDates dst ON (sales.localStandard >= dst.date1 AND sales.localDaylight <= dst.date2); proc print data=final1; format salesDate datetime18.0;
... View more
