Automatic Macros with Month conditions

[Reeza]

That's not clear. Is your question answered then? If not, please be very specific with what's needed now. It would hlep if you can show more data/code otherwise you're going to just get general approaches, which is fine, but it does take longer.

[kishoresamineni]

Hi Reeza,

My first portion which is to pull the data for last 6 months using macros is sorted out.

Pending thing : conditions where stage is 2 or 3 and month name should be highlighted in a MONTH column by using Date column
Stage conditions might be different if it is 1 then MONTH should be blank. (As shown in attachment with results example)

[Reeza]

Have you tried modifying my code?

RANGE() provides the difference between the max and min value in the row.

You need to add another ELSE IF statement (to my code) that considers if you start with 1 and end with 1 although there's a two in the middle.

[kishoresamineni]

I haven't tried that piece of code... would you please elaborate that snippet

[Reeza]

I'd rather you tried and then I'd be happy to help you fix it as needed.

[kishoresamineni]

Hi

below code which i used and worked perfectly fine for my conditions : but still i have one column OVER needs to be sorted out

data temp(drop = p_id p_stage);

set over_rated;

by id;

p_id = lag(id);

p_stage = lag(stage);

if p_id = id then do;

if p_stage < stage then output;

end;

run;

proc sql;

create table temp1 as

select id,max(stage) as stage, max(date) as date format date9d. from temp

group by id;

quit;

proc transpose data = input  out=input1(drop=_name_);

by id;

var stage;

id date;

run;

proc sql;

create table input2 as 

select a.*,b.date as month format=MONNAME9. from input1 as

a left join temp1 as b on a.id=b.id;

quit;

so far i have done till Rate column from the attachment Results section, yet i need to figure out OVER column which is nothing

but Rate-last month value(31-Oct-2019) and the OVER column should only calculate the value when Month name is available in Month column as shown in below screen shot.

can someone help me 

[kishoresamineni]

data input;
input id stage date date9.;
format date date9d.;
cards;
8923767 1 30-Apr-19
8923767 1 31-May-19
8923767 2 30-Jun-19
8923767 2 31-Jul-19
8923767 2 31-Aug-19
8923767 2 30-Sep-19
8923767 2 31-Oct-19
8923768 1 30-Apr-19
8923768 1 31-May-19
8923768 1 30-Jun-19
8923768 1 31-Jul-19
8923768 1 31-Aug-19
8923768 1 30-Sep-19
8923768 1 31-Oct-19
8923769 2 30-Apr-19
8923769 2 31-May-19
8923769 3 30-Jun-19
8923769 4 31-Jul-19
8923769 2 31-Aug-19
8923769 2 30-Sep-19
8923769 2 31-Oct-19
;
run;

data temp(drop = p_id p_stage);
set input;
by id;
p_id = lag(id);
p_stage = lag(stage);
if p_id=id then do;
if p_stage<stage then output;
end;
run;

proc sql;
create table temp1 as
select id,max(stage) as stage ,max(date) as date format date9d. from temp
group by id;

proc transpose data=input out=input1(drop = name);
by id;
var stage;
id date;
run;

proc sql;
create table input2 as
select a.*,b.date as month format=MONNAME3. from input1 as a left join temp1 as b
on a.id=b.id;
quit;

I have used above code which worked fine for my requirements till Rate column.

Now my requirement is for OVER COLUMN which is calculated as if there is any Month name in MONTH column then

RATE of that month - last month RATE

OVER = RATE - (last month)RATE (here last month is 31-Oct-2019

 Please see the attachment