topic Re: Excel formula in SAS in New SAS User

Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 11:45:10 GMT

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

Re: Excel formula in SAS

PeterClemmensen — Wed, 20 Nov 2019 11:47:55 GMT

@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 🙂

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 11:58:21 GMT

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

Re: Excel formula in SAS

PeterClemmensen — Wed, 20 Nov 2019 12:03:17 GMT

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

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 12:27:27 GMT

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

Re: Excel formula in SAS

PeterClemmensen — Wed, 20 Nov 2019 12:30:28 GMT

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?

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 12:41:43 GMT

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.

Re: Excel formula in SAS

Kurt_Bremser — Wed, 20 Nov 2019 12:43:15 GMT

From where do you deduct the month names?

Re: Excel formula in SAS

PeterClemmensen — Wed, 20 Nov 2019 12:46:17 GMT

@kishoresamineni If you want us to help you, then help us help you 🙂

You have to be specific about:

- What your initial data looks like

- The logic that you want to implement

- The desired result.

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 13:11:45 GMT

sample data

id Stage Date
22313 2 31MAY2019
22314 1 31MAY2019
22315 3 31MAY2019
22316 3 31MAY2019
22317 1 31MAY2019
22318 2 31MAY2019
22313 2 30JUN2019
22314 1 30JUN2019
22315 3 30JUN2019
22316 3 30JUN2019
22317 1 30JUN2019
22318 2 30JUN2019
22313 2 31JUL2019
22314 2 31JUL2019
22315 3 31JUL2019
22316 2 31JUL2019
22317 1 31JUL2019
22318 2 31JUL2019
22313 1 31AUG2019
22314 2 31AUG2019
22315 2 31AUG2019
22316 2 31AUG2019
22317 1 31AUG2019
22318 2 31AUG2019
22313 1 30SEP2019
22314 1 30SEP2019
22315 2 30SEP2019
22316 2 30SEP2019
22317 1 30SEP2019
22318 2 30SEP2019
22313 1 31OCT2019
22314 1 31OCT2019
22315 2 31OCT2019
22316 2 31OCT2019
22317 2 31OCT2019
22318 2 31OCT2019

Expeted Results:
ID May June July August September October Month
22313 2 2 2 1 1 1 May
22314 1 1 2 2 1 1 July
22315 3 3 3 2 2 2 May
22316 3 3 2 2 2 2 May
22317 1 1 1 1 1 2 <BLANK>
22318 2 2 2 2 2 2 <BLANK>

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 13:12:42 GMT

i have date column in my input data

Re: Excel formula in SAS

Kurt_Bremser — Wed, 20 Nov 2019 13:20:05 GMT

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

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 13:23:21 GMT

in my input data i have DATE column as:

31MAY2019

30JUN2019

31JUL2019

31AUG2019

30SEP2019

31OCT2019

these dates will be there for every ID

Re: Excel formula in SAS

PeterClemmensen — Wed, 20 Nov 2019 13:37:27 GMT

Ok. Please explain exactly how the month variable is calculated? Why is month=July for id=22314 and blank for id=22317?

Re: Excel formula in SAS

Kurt_Bremser — Wed, 20 Nov 2019 13:37:52 GMT

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

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 14:03:46 GMT

Hi draycut,

this is the business rule they follow:

id=22314 is July because ID moved from 1 to 2

business rule: any ID within last six month moves from stage 1 to 2 or 3 then that Month name should be populated and till sep'19 if any ID in stage 1 and in OCT'19 if it moves to stage 2 or 3 then Month should be blank(ID=22317)

If you see from my program even after applying lag function Month condition is not applying for some scenarios

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 14:06:34 GMT

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.

Re: Excel formula in SAS

kishoresamineni — Wed, 20 Nov 2019 14:07:48 GMT

excel formula is doing the same thing

=IF(G4<3,IFERROR(INDEX($B$1:$F$1,MATCH(3,B4:F4,0)),IF(G4<2,IFERROR(INDEX($B$1:$D$1,MATCH(2,B4:E4,0)),""),"")),"")

Re: Excel formula in SAS

Kurt_Bremser — Wed, 20 Nov 2019 14:10:38 GMT

@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?

Re: Excel formula in SAS

PeterClemmensen — Wed, 20 Nov 2019 14:20:01 GMT

Is this correct:

Considering only months may to september. If all states are constant 1 1 1 1 1 or 2 2 2 2 2, then the month should be blank.

If not, then the month should be the month where the state has the highest value. If there are multiple states that has the same value, month should be the first month where this value occurs.

Correct?