Excel formula in SAS

[kishoresamineni]

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)),""),"")),"")

[PeterClemmensen]

@kishoresamineni no doubt that the overall logic can be implemented in SAS. Seems like some kind of lookup. 

However, Excel and SAS are widely different. Thus, you should describe your problem in more detail and not just with an Excel formula you want translated 🙂

[kishoresamineni]

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

[PeterClemmensen]

What is the logic here? Why is month name='July' in the second obs but blank in the third?

[kishoresamineni]

we see the data for last six months only,

if everything is in stage 2 then it should be blank

similarly if every month is in stage 3 then it should be blank

[PeterClemmensen]

Doesn't answer why 2 2 2 1 1 1 yields a blank? Or how the months are determined when they should not be blank?

[kishoresamineni]

data temp(drop= p_id p_stage); /*temp*/
set month_fmt;
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 /*temp1*/
select id, max(stage) as stage,
max(date) as Month format date9d. from temp
group by id;
quit;

proc transpose data=month_fmt out=input1(drop= _name_); /*input1*/
by id;
var Stage;
id date;
run;

proc sql;
create table input2 as select a.*,b.Month as Month /*input2*/
format = MONNAME9. from input1 as a left join temp1 as b
on a.id=b.id;
quit;

this is the programs that i have used, i am getting the Month names but not meeting all the conditions

e.g., 1 1 1 1 2 2 should be blank but i am getting Month as September

2 3 3 3 3 3 should be blank but i am getting Month as June

3 3 3 1 2 2 should be May but i am getting September

please help me in solving the above issues.

[Kurt_Bremser]

From where do you deduct the month names?

[kishoresamineni]

i have date column in my input data

[Kurt_Bremser]

And from where do calculate WHICH of the 6 dates you have for every ID to take?

[kishoresamineni]

in my input data i have DATE column as:

31MAY2019

30JUN2019

31JUL2019

31AUG2019

30SEP2019

31OCT2019

these dates will be there for every ID

[Kurt_Bremser]

You have SIX dates in there. WHICH of these is the one to take?

[kishoresamineni]

all the six dates should be considered, because i want the report for last 6 months of data

and every account has six months data.

[Kurt_Bremser]

---

@kishoresamineni wrote:

all the six dates should be considered, because i want the report for last 6 months of data

and every account has six months data.

---

That would mean that your target variable would contain the string "MayAprilJuneJulyAugustSeptember", but it only contains ONE month name.

HOW DO YOU DETERMINE THIS MONTH NAME?

[kishoresamineni]

it contains data from May - Oct

and Month name should be picked based on the stage. 

My program worked fine for picking the Name name in Month column based on the stage ID was in.

But, some of the Month's conditions are not correct, so i have pasted the excel formula used.