topic Re: Translating sumif excel code into sas in SAS Programming

Translating sumif excel code into sas

Solly7 — Mon, 13 Sep 2021 09:44:29 GMT

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.

Re: Translating sumif excel code into sas

Sajid01 — Mon, 13 Sep 2021 11:24:57 GMT

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

Can you please verify if the above is correct?

Re: Translating sumif excel code into sas

Solly7 — Mon, 13 Sep 2021 11:30:09 GMT

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

Re: Translating sumif excel code into sas

Sajid01 — Mon, 13 Sep 2021 11:41:41 GMT

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.

Re: Translating sumif excel code into sas

ChrisNZ — Mon, 13 Sep 2021 11:45:10 GMT

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.

Re: Translating sumif excel code into sas

Solly7 — Mon, 13 Sep 2021 12:01:25 GMT

Hi, I have attached excel spreadsheet as per your request

Re: Translating sumif excel code into sas

Sajid01 — Mon, 13 Sep 2021 12:26:27 GMT

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.

Re: Translating sumif excel code into sas

Solly7 — Mon, 13 Sep 2021 12:28:18 GMT

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
;

Re: Translating sumif excel code into sas

Solly7 — Mon, 13 Sep 2021 12:31:08 GMT

Thanks a lot!!

Re: Translating sumif excel code into sas

Sajid01 — Mon, 13 Sep 2021 12:45:20 GMT

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;