Help using Base SAS procedures

Add column to multiple table based on specific criteria

Reply
Regular Contributor
Posts: 186

Add column to multiple table based on specific criteria

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.

Super User
Posts: 19,770

Re: Add column to multiple table based on specific criteria

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.

Regular Contributor
Posts: 186

Re: Add column to multiple table based on specific criteria

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

Super User
Posts: 19,770

Re: Add column to multiple table based on specific criteria

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?

Ask a Question
Discussion stats
  • 3 replies
  • 196 views
  • 0 likes
  • 2 in conversation