Converting date formats in in Sysfunc

OlsabeckT29 — Tue, 01 Nov 2022 17:11:54 GMT

I am working to automate a piece of code. When I save my sas dataset I need to have the date the data was last refreshed attached to the end of the file name. Instead of changing the code every week to the new date I would rather just have it be automated. Here is what I have:

proc sql;
	select max(date_created) as maxaddressdt 
	into :refresh separated by ""
	from mcir.new_address;
quit;

%put &refresh; */System returns 29OCT2022:05:45:08*/;

This returns the last refresh date in a date time format. I would like my file name to read as ARCHRP_20221029 for file organization purposes. Below is what I tried to reformat the date time variable to be yymmddn8. The log gave the warning that Argument 2 is out of range.

%let refreshfmt=%sysfunc(putc(&refresh,yymmddn8),8.) ;
%put &refreshfmt;


libname rawdata 'c:\data';
%macro saveloc(date=);
data rawdata.ArchRP&date (compress=binary);
	set rp;
run;
%mend saveloc;

%saveloc(date=&refreshfmt);

Thanks for the help

Re: Converting date formats in in Sysfunc

PaigeMiller — Tue, 01 Nov 2022 17:28:34 GMT

IMPORTANT POINT — do all date and time manipulations on numeric variables, not on character strings such as 29OCT2022:05:45:08

Next ... since you are using PROC SQL to extract the max DATE_CREATED (which is a numeric date/time value) just do the formatting there.

Lastly, to convert a date/time value (which is how DATE_CREATED is stored) to a formatted DATE value (which is what you want 20221029), use the DATEPART function and then format the result however you'd like.

proc sql;
    select put(datepart(max(date_created)),yymmddn8.) into :refreshfmt from mcir.new_address;
quit;
%put &=refreshfmt;

topic Converting date formats in in Sysfunc in SAS Programming

Converting date formats in in Sysfunc

Re: Converting date formats in in Sysfunc