Hi draycut, actually i looking for a month name to populate if last six month are in stage 2 0r 3 e.g. 3 3 3 2 2 2 then Month Name should be May 1 1 2 2 1 1 then Month Name should be July 2 2 2 2 2 2 then Month should be Blank 3 3 3 3 3 3 then Month should be Blank 2 2 2 1 1 1 then Month should be Blank 3 2 2 2 2 2 then Month should be May 1 1 1 1 2 2 then Month should be Blank formula what i have in excel does above conditions, now i need similar conditions to be implemented in SAS
... View more
Hi can someone help me how to write excel formula in SAS e.g., below formula i need to be implemented in SAS =IF(G8<3,IFERROR(INDEX($B$1:$F$1,MATCH(3,B8:F8,0)),IF(G8<2,IFERROR(INDEX($B$1:$D$1,MATCH(2,B8:E8,0)),""),"")),"")
... View more
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
... View more
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
... View more
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)
... View more
Hi Reeza, It did work for month part...now I could successfully pull data for last six months by using macro variables case conditions need to be sorted
... View more
Need to show Month name in a newly created MONTH column based on date column values and stage... If stage 2 in last few months then starting month of stage 2 should be in MONTH column
... View more
Hi, I am sorry I have some real-time act numbers which I can't post it here. There is the stage I am in right now ID Stage Date Need to create a new column as MONTH and need to populate month names into MONTH column based on stage and date
... View more
Hi, I was able to pull the data for last six month with macros...after that I am having some difficulty in putting the conditions that I am looking for.
... View more
Hi Reeza, would you please help me with the conditions for Month column. Month column values should be populated based on the stages whether it is in 2 or 3 and which month it has been started from 1 to 2 or 2 to 3 that month should come up in Month column.
... View more
Hi Tom, Sorry for confusing with my question.... 1. Input data is the data that i have as a source data 2. Even though the data might be for years, but i need to pull the data only for last six months for e.g., if i run the report on 1st or 2nd of November then i need to pull the data from May to October. and so on...for Dec 1st or 2nd then data from June to November 3. I need to put the conditions for stage based on the six months data that i have
... View more
Hi, I have data something like below ID, Stage, Date 1) I need to put an automatic date/month macros to pull data only for last six months. Expecting results to be like : (please see attachment) Conditions that i need : 1) Need to show only six months of data in the output 2) I need an automatic macros to pull the data only for last six months based on the input table month name 3) Stage condition: If any ID in stage one for last six months then Month should be blank (e.g., 8923768) If ID started with 1 and jumped to stage 2 then starting month of that stage 2 should be populated in Month column (e.g., 8923767) If ID started with staged 2 and moved to stage 3 then stage 3 month should be populated (e.g., 8923769) If any ID starts with stage 2 or 3 and in last month ends with 1 then Month column should be blank (e.g., 8923771)
... View more
