BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
umar_milanzi
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Manu_SAS
Obsidian | Level 7

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

10 REPLIES 10
umar_milanzi
Calcite | Level 5

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

Manu_SAS
Obsidian | Level 7

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;

umar_milanzi
Calcite | Level 5

thanks Manu

 

you guys are stars i truly appreciate this

Reeza
Super User

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

Manu_SAS
Obsidian | Level 7

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

umar_milanzi
Calcite | Level 5

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

Reeza
Super User

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. 

umar_milanzi
Calcite | Level 5

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?

Reeza
Super User

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

umar_milanzi
Calcite | Level 5

Thanks Reeza 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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