topic Re: Trouble getting a specific datetime format as a string for passing to a SPROC in SAS Studio

Trouble getting a specific datetime format as a string for passing to a SPROC

Hungry2Learn — Mon, 26 Aug 2019 14:44:17 GMT

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.

Re: Trouble getting a specific datetime format as a string for passing to a SPROC

Reeza — Mon, 26 Aug 2019 15:26:37 GMT

You would just need to change the code slightly, instead of using begin date use:

put(begin_date, FORMAT.)

You'll unfortunately likely need a picture format for that specific date format but I also know I've answered this exact question at least twice in the past month on here so it's on here if you do some search with picture formats.

https://communities.sas.com/t5/SAS-Programming/Datetime-conversion/m-p/580601/highlight/true#M164942

proc format;
picture dtStamp (round) 
low - high = '%m/%0d/%Y %0H:%0M:%0S' (datatype=datetime);
run;

Re: Trouble getting a specific datetime format as a string for passing to a SPROC

tomrvincent — Mon, 26 Aug 2019 15:38:26 GMT

Format EndDate datetime. to see it .

I'd suggest using datepart and timepart to get each in a string format you want...then concatenate them.

This should help you: https://v8doc.sas.com/sashtml/lrcon/zenid-63.htm

Re: Trouble getting a specific datetime format as a string for passing to a SPROC

Hungry2Learn — Mon, 26 Aug 2019 15:42:09 GMT

Thanks, this worked excellently for both the begin/end dates.