topic Re: change expiration indicator to day after due date in SAS Programming

change expiration indicator to day after due date

antjonz — Thu, 08 Feb 2018 20:42:31 GMT

Hi guys. I'm trying to figure out a way to show a certain date as expired the day after the due date. The back story: We keep track of training. Training is due on the last day of the month. Individuals have until the last day of the month to complete training. If not complete on the last day, the 1st day of the next month it should be expired. The way the query is set up, the training shows expired ON the last day...not the day after. So that really throws off our compliance measurements. I'm trying to figure out a way so that if the training is not complete by the 1st of the next month, then it will show expired for the previous month's measurements. I've attached a portion of the query I believe deals with the expiration date.

Re: change expiration indicator to day after due date

ballardw — Thu, 08 Feb 2018 21:29:47 GMT

2 macros, 7 proc sql calls, 1 data step, I'm not counting the variables, and no comments as to what any of this does, and no place I can tell where a variable is actually assigned as expired or which variable holds a likely date.

Which variable contains the date information that should be one day later?

Re: change expiration indicator to day after due date

SuryaKiran — Thu, 08 Feb 2018 21:40:50 GMT

The code you posted will not help anything until you give some sample data you have and how you want the output.

Did you look at SAS function INTNX() where you can get the start or end date of the month with interval.

Re: change expiration indicator to day after due date

ShiroAmada — Fri, 09 Feb 2018 09:09:22 GMT

In a nutshell, try this.

data WANT;
  due_dt='01mar2017'd;
  exp_dt=intnx('day',due_dt,1);
format due_dt exp_dt date9.;
run;

Hope it helps.

Re: change expiration indicator to day after due date

antjonz — Fri, 09 Feb 2018 13:14:12 GMT

my apologies. I'm still kinda new to SAS. I've attached an excel output of the raw data that is returned when I run the query. The "Due Date" column is the end of the applicable month, and the "status" column shows "expired". It should not expire on the due date if it hasn't been done yet. I want it to show expired the next day if not done.

Re: change expiration indicator to day after due date

antjonz — Fri, 09 Feb 2018 13:32:02 GMT

Thanks for the reply. I'm still kinda new at this. Please see below.

macro set_up_dates();
%global _dt_0dy _dt_0dy_sas
_dt_0yr _dt_0yr_sas
_dt_0ye _dt_0ye_sas;
data _null_;
m = tranwrd(put(month(&rundate.), 2.), ' ', '0');
d = tranwrd(put(day(&rundate.), 2.), ' ', '0');
y = put(year(&rundate.), 4.);
call symput('_dt_0dy_sas', &rundate.);
call symput('_dt_0dy', compress("'"||y||'-'||m||'-'||d||"'"));

m = '01';
d = '01';
y = put(year(&rundate.), 4.);
call symput('_dt_0yr_sas', mdy(1,1,year(&rundate.)));
call symput('_dt_0yr', compress("'"||y||'-'||m||'-'||d||"'"));

m = '12';
d = '31';
y = put(year(&rundate.), 4.);
call symput('_dt_0ye_sas', mdy(12,31,year(&rundate.)));
call symput('_dt_0ye', compress("'"||y||'-'||m||'-'||d||"'"));
run;

%mend;
%macro get_base_data();
proc sql;
connect to teradata (tdpid = 'dwprod' user = &terauser_prd. password = &terapass_prd. mode=teradata);
create table _calendar as
select *
from connection to teradata
(
LOCKING TechOps_DataLab.TBL_AC_OPSSPEC FOR ACCESS
select extract(year from cal_dt) as cal_yr
,extract(month from cal_dt) as cal_mo
,count(1) as cal_dy_cnt
from stdmv.calendar
where cal_dt between date &_dt_0yr. and date &_dt_0ye.
group by 1, 2
order by 1, 2
);

Re: change expiration indicator to day after due date

ballardw — Fri, 09 Feb 2018 16:37:48 GMT

@antjonz wrote:

Thanks for the reply. I'm still kinda new at this. Please see below.

macro set_up_dates();
%global _dt_0dy _dt_0dy_sas
_dt_0yr _dt_0yr_sas
_dt_0ye _dt_0ye_sas;
data _null_;
m = tranwrd(put(month(&rundate.), 2.), ' ', '0'); <= What does RUNDATE look like, not shown anywhere
d = tranwrd(put(day(&rundate.), 2.), ' ', '0');
y = put(year(&rundate.), 4.);
call symput('_dt_0dy_sas', &rundate.);
call symput('_dt_0dy', compress("'"||y||'-'||m||'-'||d||"'")); <= if your "rundate" is an actual sas date value then a format would take care of all the m, d, y stuff

m = '01';
d = '01';
y = put(year(&rundate.), 4.);
call symput('_dt_0yr_sas', mdy(1,1,year(&rundate.)));
call symput('_dt_0yr', compress("'"||y||'-'||m||'-'||d||"'")); <= if your rundate is a sas date value then this value is intnx('year',rundate,0,'B');

m = '12';
d = '31';
y = put(year(&rundate.), 4.);
call symput('_dt_0ye_sas', mdy(12,31,year(&rundate.)));
call symput('_dt_0ye', compress("'"||y||'-'||m||'-'||d||"'")); <= if your rundate is a sas date value then this value is intnx('year',rundate,0,'E');
run;

%mend;
%macro get_base_data();
proc sql;
connect to teradata (tdpid = 'dwprod' user = &terauser_prd. password = &terapass_prd. mode=teradata);
create table _calendar as
select *
from connection to teradata
(
LOCKING TechOps_DataLab.TBL_AC_OPSSPEC FOR ACCESS
select extract(year from cal_dt) as cal_yr
,extract(month from cal_dt) as cal_mo
,count(1) as cal_dy_cnt
from stdmv.calendar
where cal_dt between date &_dt_0yr. and date &_dt_0ye.
group by 1, 2
order by 1, 2
);

It looks like your data _null_ might be reducible to

%let rundate= '15Mar2017'd;
data _null_;
   call symputx('_dt_0dy_sas', &rundate.);
   Call symputx('_dt_0dy',put(&rundate,yymmddD10.));
   call symputx('_dt_0yr_sas', intnx('year',&rundate,0,'B'));
   call symputx('_dt_0yr',put(intnx('year',&rundate,0,'B'),yymmddD10.));
   call symputx('_dt_0ye',put(intnx('year',&rundate,0,'E'),yymmddD10.));
   call symputx('_dt_0ye_sas', intnx('year',&rundate,0,'E'));
run;

%put &dt_0dy &_dt_0yr &_dt_0ye;

However I don't think you have indemnified the variable that needs to be "incremented"

Re: change expiration indicator to day after due date

antjonz — Fri, 09 Feb 2018 17:47:25 GMT

Thanks! That's helpful. to answer your question about the indemnified variable, I believe the variable is the "status". The status will show expired based on the due date. For example, a course is due every 12 months and it's due the last day of that 12th month. However, the status is showing expired on that last day when it shouldn't actually exire until the NEXT day:

proc sql;
create table _course_summary as
select year(file_dt) as yr
,month(file_dt) as mo
,o.delta_department
,o.contractor_ind
,o.hr_stn_cd
,sum(case when o.status = 'EXPIRED' then 1 else 0 end) as c_expired
,count(distinct case when o.status = 'EXPIRED' then o.empl_nbr else '' end) as e_expired
from _lms_raw o
where o.status = 'EXPIRED'
group by 1, 2, 3, 4, 5
order by 1, 2, 3, 4, 5;
create table _dep_summary as
select p.delta_department
,p.contractor_ind
,p.hr_stn_cd
,count(distinct p.p_unique_key) as emp_cnt
from trn._person p
where p.delta_department not in ('','XXX')
group by 1, 2, 3
order by 1, 2, 3;
run;