I have a macro that calls a SPROC to a SQL Server. The program wrapped around it calls this macro for several different categorical values using the following code: data _null_;
set work.categoryTable;
endDate = datetime();
call execute('%queryData(Categ='|| categ ||', BeginDate='|| beginDate ||', EndDate='|| endDate ||');');
run; Code within the queryData macro appends all of the queried data from each iteration into a singular table. I have an existing table that I queried with hard coded date strings. This table has datetime values within it, and I pull the most recent from said table and match it to work.categoryTable as appropriate, which is where the beginDate variable comes from. The goal here is to access the new data that isn't yet present in the main table. This is a program that I intend to set up using the jobs system to run daily, which means that I can't hard code the date strings moving forward. The problem that I'm running into is how to take the beginDate and endDate and pass them to the macro as a string in the format that will work with the SPROC. Additionally, I didn't create the SPROC so I don't have much flexibility in the format of the date string. The format that I need to pass as a string looks akin to this: '8/26/2019 10:30:00' At the time that the call statement is processed, the table set from categoryTable and modified with the endDate variable looks like this: CATEG | BeginDate | EndDate
categ1 14AUG2019:23:56:15.000 1882433719.2
categ2 14AUG2019:22:32:19.000 1882433719.2
categ3 12AUG2019:14:44:52.000 1882433719.2 Where BeginDate is classified as a 'Time' variable when hovering over the column name and EndDate is a 'numeric'. Thanks in advance for the assistance.
... View more
