Convert Macro String to SAS Date

Question

I've got a macro variable that takes a string (e.g., '1-1-2014') to feed into a SQL query. I then want to include that date, without the single quotation marks and formatted differently, as a title on my reports. How would I go about changing the string '1-1-2014' (again, with single quotation marks) to January 1, 2014 (worddate format).

%let date = '1-1-2014';
title "Report as of [Conversion Code Here]";

Of course, if you know of a better/more efficient way to do what I'm getting at, I'm all ears. (My program does require pulling data out of SQL Server, which is why I have the macro variable formatted with the single quotes around it.)

Thanks in advance.

(I searched through existing questions and couldn't find an answer to this. My apologies if I missed something.)

SRSwift SRSwift · Accepted Answer · 2014-09-30T19:24:55

For format conversion you need the put() for numeric to character conversion and the input() function for character to numeric. To use SAS data step functions in macro calls the %sysfunc() macro function is required. In %sysfunc() replace put()/input() with putn()/putc()/inputn()/inputc() as per here. %sysfunc() also allows a format to be specified for it's output as a second argument.

%let date = '1-1-2014'; 
%let longDate = %sysfunc(inputn(%sysfunc(compress(&date., "'")), ddmmyy10.), WEEKDATE32.);
%put &longDate.;

The compress() function is simply stripping the quotes from your original macro variable, so that the date can be read using the ddmmyy10. informat.

Edit: Step by step breakdown.

%let date = '1-1-2014'; 
%put &date.;
/* Strip away the single quotes */
%let deQuotedDate = %sysfunc(compress(&date., "'"));
%put &deQuotedDate.;
/* Read in the date using ddmmyy. informat and convert to SAS date */
%let sasDate = %sysfunc(inputn(&deQuotedDate., ddmmyy10.));
%put &sasDate.;
/* Convert the SAS date to the required format */
%let longDate = %sysfunc(putn(&sasDate., weekdate32.));
%put &longDate.;

Convert Macro String to SAS Date

1 Answers