BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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.

3 REPLIES 3
Reeza
Super User

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.

nicnad
Fluorite | Level 6

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

Reeza
Super User

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?

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!

What is Bayesian Analysis?

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.

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
  • 3 replies
  • 743 views
  • 0 likes
  • 2 in conversation