BookmarkSubscribeRSS Feed
antjonz
Calcite | Level 5

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.  

7 REPLIES 7
ballardw
Super User

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?

 

 

antjonz
Calcite | Level 5

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

ballardw
Super User

@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" 

antjonz
Calcite | Level 5

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;

SuryaKiran
Meteorite | Level 14

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
antjonz
Calcite | Level 5

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. 

ShiroAmada
Lapis Lazuli | Level 10

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1239 views
  • 0 likes
  • 4 in conversation