topic Re: help with macro to convert date from gmt to cst in SAS Programming

help with macro to convert date from gmt to cst

DanD999 — Sun, 22 Mar 2020 04:44:53 GMT

The macro I'm using was written by someone else. It's been in use for years so I didn't anticipate any problems but when a date is missing it creates a date of 12/31/1959 and a time of 19:00. At first I put an if statement in my data step and that didn't work so I tried putting it in the macro but that didn't work either.

Original macro:

%macro make_cst_from_gmt(dttm);
	/* Change the date to CST from GMT */
	/* nwkdom(2, 1, 3, year(datepart(&dttm))); */ /* DST begins 2nd Sun in March */
	/* nwkdom(1, 1,11, year(datepart(&dttm))); */ /* DST ends 1st Sun in Nov     */
	if nwkdom(2, 1, 3, year(datepart(&dttm))) <= datepart(&dttm) <= nwkdom(1, 1,11, year(datepart(&dttm))) then do;
		&dttm = sum(&dttm, -"5:00:00"t);
	end;
	else do;
		&dttm = sum(&dttm, -"6:00:00"t);
	end;
%mend make_cst_from_gmt;

Original macro to change dates:

data table1A;
	set table1;
    format DECN_DT  mmddyy10.;
	format DECN_TM  time8.;
    %make_cst_from_gmt1(dttime);
    DECN_DT = datepart(dttime);
    DECN_TM = timepart(dttime);
run;

Then I tried something like this by changing the original macro that does the conversion by adding an "if" statement. I also tried adding an "if" statement to my data step but I still get the date and time when the column has no data.


%macro make_cst_from_gmt1(dttm);
	/* Change the date to CST from GMT */
	%if dttm > 0 %then %do;
		if nwkdom(2, 1, 3, year(datepart(&dttm))) <= datepart(&dttm) <= nwkdom(1, 1,11, year(datepart(&dttm))) then do;
			&dttm = sum(&dttm, -"5:00:00"t);
		end;
		else do;
			&dttm = sum(&dttm, -"6:00:00"t);
		end;
	%end;
%mend make_cst_from_gmt1;

data table1A;
	set table1;
    format DECN_DT  mmddyy10.;
	format DECN_TM  time8.;
    %make_cst_from_gmt1(dttime);
    DECN_DT = datepart(dttime);
    DECN_TM = timepart(dttime);
run;

data table1A;
	set table1;
    format DECN_DT  mmddyy10.;
	format DECN_TM  time8.;
	%if dttime = . %then %do;
		%put 'this one is missing ===> ' pgm_id;
		%put 'decn ===> ' dttime;
		DECN_DT = .
		DECN_TM = .
	%end;
	%else %do;
      %make_cst_from_gmt1(dttime);
      DECN_DT = datepart(dttime);
      DECN_TM = timepart(dttime);
	%end;
run;

Here's some test data I created for this:

data table1;
infile datalines;
input did pgm_id user $ dttime DATETIME24.3; 
format dttime DATETIME24.3;
return;
datalines;
8 63832680 DAN 23AUG2013:19:42:55.160
2 63832680 JAN 01AUG2013:19:20:58.785
6 63832680 DAN 
4 63832680 DAN 13AUG2013:19:29:39.578
7 63832680 DAN 23AUG2013:15:30:00.452
9 63832680 DAN 
1 63832680 BOB 14AUG2013:22:24:41.894
5 63832680 DAN 
3 63832680 DAN 08AUG2013:19:13:00.356
;
run;

Thanks

Re: help with macro to convert date from gmt to cst

Patrick — Sun, 22 Mar 2020 05:39:58 GMT

The actual logic within the macro executes on SAS data step level and though you also need to build your logic on this level. Adding a simple check for a missing value for the SAS variable to which name macro variable &dttm resolves, will do the trick.

Required change added to below.

%macro make_cst_from_gmt(dttm);
  /* Change the date to CST from GMT */
  /* nwkdom(2, 1, 3, year(datepart(&dttm))); */
  /* DST begins 2nd Sun in March */
  /* nwkdom(1, 1,11, year(datepart(&dttm))); */
  /* DST ends 1st Sun in Nov     */
  if missing(&dttm) then; /* do nothing */
  else
  if nwkdom(2, 1, 3, year(datepart(&dttm))) <= datepart(&dttm) <= nwkdom(1, 1,11, year(datepart(&dttm))) then
    do;
      &dttm = sum(&dttm, -"5:00:00"t);
    end;
  else
    do;
      &dttm = sum(&dttm, -"6:00:00"t);
    end;
%mend make_cst_from_gmt;

Given that the macro is already in use for years, eventually in a macro library and not that easily changed, another and eventually even better way is to check the variable for missing before calling the original unchanged macro. This also avoids that you then apply the datepart() function for missings - or you would have to implement such a missing check there anyway.

data table1A;
  set table1;
  format DECN_DT  mmddyy10.;
  format DECN_TM  time8.;
  if not missing(dttime) then
    do;
      %make_cst_from_gmt(dttime);
      DECN_DT = datepart(dttime);
      DECN_TM = timepart(dttime);
    end;
run;

Re: help with macro to convert date from gmt to cst

DanD999 — Sun, 22 Mar 2020 05:56:41 GMT

I had the right idea but poor execution. Thanks so much Patrick for the solution and quick response.