topic Turning sas date into character inside a macro in SAS Programming

Turning sas date into character inside a macro

Ju_Fr — Wed, 26 Oct 2022 18:00:34 GMT

Hello everyone,

I feel like my issue is a simple one yet I have been stuck on this for hours. I am trying to convert a SAS date variable (datetime20. format) into a string of characters YYYYMMDD inside a macro (code lines in bold).

My macro calculates the number of months between my start and end date and then executes another macro (%request) at each incremented month. I need to transform my start date (a SAS date) to a string YYYYMMDD so that the second macro will run.

When I run my programme I get stuck at the first loop and I come across this note :

NOTE : Macro variable CHAR_DAY resolves to inputn('01JUL2014'd,yymmddn8.). It doesn't look like it's converting my date to a string.

Could anyone help?

Many thanks,

Juliette

%macro hormono(start_dt=,end_dt=);

%let nbmonths=%eval(%sysfunc(intck(month,&start_dt.,&end_dt.)));

%do i=0 %to &nbmonths;
%if &i=0 %then %do;
%let char_day=inputn(&start_dt.,yymmddn8.);
%request(database, &char_day.);
%end;
%else %do;
%let char_day2=inputn(%sysfunc(intnx(month,&start_dt,&i,s)),yymmddn8.);
%request(temp, &char_day2.);
proc sql;
insert into database
select * from temp;
quit;
%end;
%end;

%mend hormono;

Re: Turning sas date into character inside a macro

SASKiwi — Wed, 26 Oct 2022 19:57:53 GMT

Perhaps something like this?

%let start_dt = %sysfunc(datetime(), datetime20.);
%put start_dt = &start_dt;
%let char_day = %sysfunc(datepart("&start_dt"dt), yymmddn8.);
%put char_day = &charday;

Re: Turning sas date into character inside a macro

Reeza — Wed, 26 Oct 2022 20:02:04 GMT

Can you show how the macro is being called?
And what you want to pass to REQUEST?

You're nesting the %SYSFUNC() and INPUTN() incorrectly but not 100% sure I understand what is the input. INPUT assumes that your date comes in as YYMMDD (2014-07-01) but you said it's a datetime? If so, the datetime also requires to be converted to a date type variable.

Re: Turning sas date into character inside a macro

Tom — Wed, 26 Oct 2022 20:15:52 GMT

You can use INTCK() to count the number of months and INTNX() to generate the months.

But if you have two DATETIME values (which are NOT dates) you need to use the DTMONTH interval and not the MONTH interval.

%macro hormono(start_datetime,end_datetime);
%local offset  ;
%do offset = 0 %to %sysfunc(intck(dtmonth,&start_datetime,&end_datetime));
  %request(temp, %sysfunc(datepart(%sysfunc(intnx(dtmonth,&start_datetime,&offset))),yymmddn8.))
  proc append base=database data=temp;
  run;
%end;
%mend hormono;

%hormono('01JAN2020:00:00'dt,'01DEC2020:00:00'dt)

Re: Turning sas date into character inside a macro

ballardw — Wed, 26 Oct 2022 21:45:05 GMT

First issue is this: "convert a SAS date variable (datetime20. format) "

SAS Date variables are measured in DAYS, Datetime values are measured in seconds. Since the units are different you have to pay attention to parameters used. If apply a datetime20 format to a date value you typically see results in the early 1960's, the other way around, applying a date format to a datetime you typically get out of range results for recent dates.

In the INTNX and INTCK functions if the value is a DATETIME then you prefix intervals that involve the date portion with DT: dtday, dtmonth, dtyear and such. The RESULT is still a date time with INTNX. To get the DATE part from a DATETIME value you use the DATEPART function. INPUTING the result of INTNX is just wrong, it is a date, time or datetime value and one that if an informat actually returns a result will almost never be the date, time or datetime you think it should.

Example to consider:

data example;
   x="01Jan1960:12:00:00"dt;
   y="01MAR1960:12:00:00"dt;
   format x y datetime20.;
   z1= intck('month',x,y);
   z2= intck('dtmonth',x,y);
   put z1= z2= ;
run;

These datetime values are pretty obviously 2 months apart. If you use MONTH for the interval the result, as in Z1 above, is 170320. Z2=2.

INPUTN is a function, just like INTCK. So you need to use %sysfunc (inputn(<stuff>) )

Your partial LOG strongly implies that you passed a character value and Input with something that looks like '01JUL2014'd is just wrong. That is a date literal which SAS treats as date. Trying to input with a YYMMDDN just compounds the issue because '01JUL2014'd starts with '01, not a year, the month is the 3-letter abbreviation not the 2 digits and the ending year is just not going to work with that informat.

With that said, you need to show some actual values used as your parameters Start_dt and End_dt and tell use what the expected result is for those.

Re: Turning sas date into character inside a macro

Kurt_Bremser — Wed, 26 Oct 2022 20:28:56 GMT

Here you assume that &start_dt is either a raw SAS date value, or a valid date literal:

%eval(%sysfunc(intck(month,&start_dt.,&end_dt.)))

But here you assume it is a string containing a date in YYYYMMDD notation:

%let char_day=inputn(&start_dt.,yymmddn8.)

This function call would not work anyway, for two reasons: no %SYSFUNC wrapper, and there is no YYMMDDN informat.