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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: