Add column to multiple table based on specific criteria

nicnad — Tue, 11 Mar 2014 22:06:21 GMT

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.

Re: Add column to multiple table based on specific criteria

Reeza — Tue, 11 Mar 2014 22:33:46 GMT

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.

Re: Add column to multiple table based on specific criteria

nicnad — Wed, 12 Mar 2014 18:20:28 GMT

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

Re: Add column to multiple table based on specific criteria

Reeza — Wed, 12 Mar 2014 18:22:31 GMT

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?

topic Add column to multiple table based on specific criteria in SAS Procedures

Add column to multiple table based on specific criteria

Re: Add column to multiple table based on specific criteria

Re: Add column to multiple table based on specific criteria

Re: Add column to multiple table based on specific criteria