Hi,
I have the following tables :
KPI.ACTION_LOG
KPI.CREATE_ALERT
KPI.CREATE_CASE
KPI.ACTION_TAKEN
Each of those table contains a timestamp. This variable is either called CREATION_TIMESTAMP or LOGTIMESTAMP.
I'd like to create a SAS procedures that would loop through each of those tables and create several columns based on the logic given below.
In the following logic, I use the MAX() function in order to get my year or month based on the logtimestamp or the creation_timestamp, since if the value is missing the other variable will be kept. I'm pretty sure this is not the right way to do it. I also have no idea how to loop through the 4 tables mentioned above in order to add the different columns to each of them.
My logic :
data WORK.extract_final;
set KPI.ACTION_LOG;
YEAR = MAX(YEAR(DATEPART(LOGTIMESTAMP)),YEAR(DATEPART(CREATION_TIMESTAMP)));
MONTH = MAX(MONTH(DATEPART(LOGTIMESTAMP)),MONTH(DATEPART(CREATION_TIMESTAMP)));
if month = 11 then do; mois_compt = 1; trim_compt= 'Q1'; annee_compt = year+1; end;
if month = 12 then do; mois_compt = 2; trim_compt= 'Q1'; annee_compt = year+1; end;
if month = 1 then do; mois_compt = 3; trim_compt= 'Q1'; annee_compt = year; end;
if month = 2 then do; mois_compt = 4; trim_compt= 'Q2'; annee_compt = year; end;
if month = 3 then do; mois_compt = 5; trim_compt= 'Q2'; annee_compt = year; end;
if month = 4 then do; mois_compt = 6; trim_compt= 'Q2'; annee_compt = year; end;
if month = 5 then do; mois_compt = 7; trim_compt= 'Q3'; annee_compt = year; end;
if month = 6 then do; mois_compt = 8; trim_compt= 'Q3'; annee_compt = year; end;
if month = 7 then do; mois_compt = 9; trim_compt= 'Q3'; annee_compt = year; end;
if month = 8 then do; mois_compt = 10; trim_compt= 'Q4'; annee_compt = year; end;
if month = 9 then do; mois_compt = 11; trim_compt= 'Q4'; annee_compt = year; end;
if month = 10 then do; mois_compt = 12; trim_compt= 'Q4'; annee_compt = year; end;
quarter = catx('-',annee_compt,trim_compt);
run;
I hope you understand what I am trying to accomplish.
Thank you for your help and time.
You can use intnx and formats to do this rather than multiple lines of code.
date_max=datepart(max(dt1, dt2));
new_date=intnx('month', date_max, 2);
mois_compt=month(new_date);
quarter=put(mois_compt, yyqd7.);
A starting point at least.
I don't think you code achieve what I want to do.
In fact when month() of a timestamp = 11, I want mois_compt to equal 1.
Let me know if you need further explanations
You're incrementing it by 2 months, which is why the intnx function increments the date by 2 months.
Have you tried it and it doesn't work?
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.