Translating sumif excel code into sas

[Solly7]

Hi, i need help in translating the below excel code into SAS code..please note that the M = final_score(Contains 7 rows with scores from 0 to 5) and N2 = Total_Amount( 40 000)

(IF(M2>=3,$N$2/SUMIF($M$1:$M$7,">=3")*M2,0)

Looking forward to your help.

[Sajid01]

SUMIF($M$1:$M$7,">=3")*M2,0

Can you please verify if the above is correct?

[Solly7]

Hi thanks for your reply, apologies we should replace M2 with only M
SUMIF($M$1:$M$7,">=3")*M,0

[Sajid01]

Please help understand this term

$N$2/SUMIF($M$1:$M$7,">=3")*M

Looking at denominator, if the sum of the seven rows is greater or equal to 3, use the sum of the rows and multiply by M.
Does that mean multiply every member of every row (M! to M7) with the Sum.
IF the sum is less than 3, then does it not mean zero in the denominator ("Division by zero").
Please attach a sample spreadsheet.

[Solly7]

Hi, I have attached excel spreadsheet as per your request

[ChrisNZ]

What does the data look like? 

Where's the single N2 value stored if you have 7 observations?

Show us a before table (as SAS code) and a wanted table.

[Sajid01 · Accepted solution]

Here is the code that does the job

proc sql;
select sum(M) into :sum_m from have
where M>= 3;
quit;

data want;
set have;
if M > 3 then do
new_var=round((40000/input(&sum_m,best12.))*M,3);
end;
else new_var=0;
run;

The output is as follows and matches the values in your excel sheet.

Please let me know if there are questions.

[Solly7]

Thanks a lot!!

[Solly7]

Hi Chris, see below sas code as per your request..the total amount variable is defined as macro variable..

%let Total_Amount= 40000;



data HAVE;
input Final_Score user : $20.;
datalines;
3 ALUPIYA
2 DLOUIS
5 DMORAKE
4 FDANISA
1 FFARAO
2 SSEBELA
4 WSMITH
;



data WANT;
input Final_Score user : $20. Amount;
datalines;
3 ALUPIYA 7500
2 DLOUIS 0
5 DMORAKE 12500
4 FDANISA 10000
1 FFARAO 0
2 SSEBELA 0
4 WSMITH 10000
;

[Sajid01]

Sorry in the morning rush I missed the first step of the code. That I am presenting below

data have;
input M;
datalines;
3.20
2.00
4.00
1.00
5.00
2.00
4.00
5.00
2.00
1.00
3.50
;
run;