comparing 2 amount values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

comparing 2 amount values

hi ,

 

iam trying to monitor some financial figures. i have 2 values that i need to work with but i just dont have an idea on how i will write this interms of sas coding. please help

 

my values are as follows

 

Trans_Type

Amount

salary

10000

Cheq dep

1000

Teller dep

5000

 

my desire results is that any amount that is more than 20% of the salary should be flagged 1. and any amount below 20% of the salary should be flagged 0. anyone please help urgently!!!

 

thanks in advance


Accepted Solutions
Solution
‎04-26-2016 02:20 AM
Contributor
Posts: 20

Re: comparing 2 amount values

In that case, it will not work. Even i thought the same. We can do one thing, capture the salary in macro variable and using it in the datastep.

 

data m1;
input type $ amount;
cards;
sal 10000
cheq 2000
teller 5000
;

proc sql noprint;
select amount into : amt from m1 where type='sal';
quit;

data m2;
set m1;
x=&amt.;
if amount/x > 0.20 then flag=1; else flag=0;
run;

 

~Manohar

View solution in original post


All Replies
Occasional Contributor
Posts: 17

comparing 2 amount values

Posted in reply to umar_milanzi

hi ,

 

iam trying to monitor some financial figures. i have 2 values that i need to work with but i just dont have an idea on how i will write this interms of sas coding. please help

 

my values are as follows:

 

Trans_Type      Amount

Salary                  10000

Cheq Dep             1000

Sandry Dep          2000


my desire results is that any amount that is more than 20% of the salary should be flagged 1. and any amount below 20% of the salary should be flagged 0. anyone please help urgently!!! 

 

thanks in advance

Super User
Posts: 19,869

Re: comparing 2 amount values

Posted in reply to umar_milanzi

Please post some more sample data and expected output. You'll need to modify it to match your data structure but it should help you get started. 

 

Proc SQL;
Create table want as
Select a.id, a.type, a.amount, case when  a.amount/b.amount >= 0.2 then 1 else 0 as flag
From have as a
Left join have as b
On a.id = b.id
And a.type ne 'Salary' and b.type. = 'Salary';
Quit;

You our can merge data with itself via SQL and then do calculation. 

Occasional Contributor
Posts: 17

Re: comparing 2 amount values

Hi Reeza

 

thanks for the clarity.

 

i see that your code joins two tables. how will the code be if the data is contained in one table. like in my sample code provided. how can i compare first value of 'Salary' to other values?

Super User
Posts: 19,869

Re: comparing 2 amount values

Posted in reply to umar_milanzi

Take a closer look at the code. It joins the table Have to itself, Have.  

Occasional Contributor
Posts: 17

Re: comparing 2 amount values

Thanks Reeza 

Occasional Contributor
Posts: 17

comparing 2 amount values

Posted in reply to umar_milanzi

hi ,

 

iam trying to monitor some financial figures. i have 2 values that i need to work with but i just dont have an idea on how i will write this interms of sas coding.

 

my values are as follows

 

Trans_Type

Amount

salary

10000

Cheq dep

1000

Teller dep

5000

 

my desire results is that any amount that is more than 20% of the salary should be flagged 1. and any amount below 20% of the salary should be flagged 0. anyone please help urgently!!!

 

thanks in advance

Contributor
Posts: 20

Re: comparing 2 amount values

Posted in reply to umar_milanzi

data m1;
input type $ amount;
cards;
sal 10000
cheq 2000
teller 5000
;
run;

data m2;
set m1;
if type='sal' then x=amount;
retain x;
if amount/x > 0.20 then flag=1; else flag=0;
run;

proc print;run;

Occasional Contributor
Posts: 17

Re: comparing 2 amount values

thanks Manu

 

you guys are stars i truly appreciate this

Super User
Posts: 19,869

Re: comparing 2 amount values

What if Salary isn't in the first record but a later record?

Solution
‎04-26-2016 02:20 AM
Contributor
Posts: 20

Re: comparing 2 amount values

In that case, it will not work. Even i thought the same. We can do one thing, capture the salary in macro variable and using it in the datastep.

 

data m1;
input type $ amount;
cards;
sal 10000
cheq 2000
teller 5000
;

proc sql noprint;
select amount into : amt from m1 where type='sal';
quit;

data m2;
set m1;
x=&amt.;
if amount/x > 0.20 then flag=1; else flag=0;
run;

 

~Manohar

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 549 views
  • 1 like
  • 3 in conversation