DATA Step, Macro, Functions and more

change expiration indicator to day after due date

Reply
Occasional Contributor
Posts: 6

change expiration indicator to day after due date

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.  

Attachment
Super User
Posts: 13,507

Re: change expiration indicator to day after due date

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?

 

 

Occasional Contributor
Posts: 6

Re: change expiration indicator to day after due date

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
);

Super User
Posts: 13,507

Re: change expiration indicator to day after due date

[ Edited ]

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" 

Occasional Contributor
Posts: 6

Re: change expiration indicator to day after due date

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;

Valued Guide
Posts: 590

Re: change expiration indicator to day after due date

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. 

Thanks,
Suryakiran
Occasional Contributor
Posts: 6

Re: change expiration indicator to day after due date

Posted in reply to SuryaKiran

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. 

Frequent Contributor
Posts: 116

Re: change expiration indicator to day after due date

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.

Ask a Question
Discussion stats
  • 7 replies
  • 159 views
  • 0 likes
  • 4 in conversation