DATA Step, Macro, Functions and more

How to calculate ratio between two variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

How to calculate ratio between two variables

[ Edited ]

Hi..!

 

please help in calculating the ratio of 2 variables

 

ex...  ID   Present    Total

         22    12            30

         28     20           50

 

and so on.....!

 

I want the ratio of the 2 variables (Present, Total) with adding a ratio column.

 

Note :- data consists of more than 100 variables.

 

 


Accepted Solutions
Solution
‎03-10-2017 08:05 AM
Super User
Super User
Posts: 7,401

Re: How to calculate ratio between two variables

Well, I have manually typed in this information:

 

data ans2;
  input id present total;
  ratio=floor(total/present);
datalines;
54734 25000 85000
55742 7000 65000
;
run;

Which works perfectly for me results in a dataset called ans2:

 

id        present  total     ratio

54734  25000    85000  3
55742  7000      65000  9

 

Please explain what you are doing exactly, start by using the code window {i} above your post to put actual text code in rather than pictures which we then have to manually tpye in.

 

View solution in original post


All Replies
PROC Star
Posts: 551

Re: How to calculate ratio between two variables

Here is the first bit of what you are asking.

 

data have;
   input ID Present Total;
   datalines;
   22 12 30
   28 20 50
   ;

data want;
   set have;
   ratio = Total/Present;
run;

Not sure about what you mean by adding a ratio column against ID though? Smiley Happy

Occasional Contributor
Posts: 12

Re: How to calculate ratio between two variables

Thanks for the statement..

My data is consists of 100+ values and I need to find the ratio of it.
Super User
Super User
Posts: 7,401

Re: How to calculate ratio between two variables

In future show test data in the form of a datastep as I show below, and show what you want the output to look like:

data have;
  input id present total;
  ratio=floor(total / present);
datalines;
22    12            30
28     20           50
;
run;
Occasional Contributor
Posts: 12

Re: How to calculate ratio between two variables

Hiii.....!

My data consists of 100+ values and we need to find the ratio of mentioned 2 columns
Super User
Super User
Posts: 7,401

Re: How to calculate ratio between two variables

[ Edited ]

Anil9 wrote:
Hiii.....!

My data consists of 100+ values and we need to find the ratio of mentioned 2 columns

 

Edit, reading your other post it seems like your not telling us the whole story.  Start by posting a datastep with test data which clearly defines your issue.  The provide an example of what the output should look like.  Also describe any logic between the two.

 

How does having other variables in the dataset affect the functionality which I provided?  It doesn't matter if you have only those two or a million variables, its still:

  ratio=floor(total / present);

Unless your not telling us the whole story? 

 

Occasional Contributor
Posts: 12

Re: How to calculate ratio between two variables

Hi... Thanks for the instructions.

 

I have tried entering of first 100 values it went well, But when I have entered 13,000 values it was showing blanks in the column.

Super User
Super User
Posts: 7,401

Re: How to calculate ratio between two variables

Sorry, "I have tried entering of first 100 values it went well", what do you mean you have entered it?  You are using SAS aren't you?  I.e. you have taken the data from the CSV file and imported it to a SAS dataset, then you run some code like:

data want;
  set have;
  ratio=total / present;
run;

Where have is the dataset you created.  You will then have another dataset called want which contains your original data and another column call ratio which contains the ratio per observation.  If you have missing of either present or total then the resulting ratio will be missing.

Contributor
Posts: 43

Re: How to calculate ratio between two variables

Proc sql;
create table want as
select id,
present,
sum(present) as present_sum,
total,
sum(total) as total_sum
from have
group by id;
quit;

Data want1;
Set want;
Present_rate=present/present_sum;
Total_rate=total/total_sum;
Run;
Super User
Posts: 10,497

Re: How to calculate ratio between two variables

Do mean that TOTAL is supposed to be the sum of 100 other variables? Or are there 100 variables that need to have a ratio caclulated?

Do you want the calculation of the ratio per record? Or is total supposed to be the Total of some variable for all records in a data set?

 

Provide a small data set, say maybe 5 rows and 5 variables. Show what the data looks like at input. Then provide another example data set with the results that you can calculate by hand shown.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Occasional Contributor
Posts: 12

Re: How to calculate ratio between two variables

Ok... Let's be clear

 

I have a .csv data which consists of 13,000 entries and I need to find the ratio of two columns of (Present vs Total) in SAS.

 

File example...

 

ID      Present     Total

1           22           55

2           125         597

.

.

.

.

.till

13000 entries.....

 

*I'm new to SAS. 

Super User
Super User
Posts: 7,401

Re: How to calculate ratio between two variables

Sorry, you haven't answered the question.  Do you want ratio on each observation, if so:

ration=total / present;

 

Or do you want some sort of summing up and then doing ratio.  Post example test data - in the form of a datastep - and what the output should look like.  Good question will result in good answers.

Occasional Contributor
Posts: 12

Re: How to calculate ratio between two variables

Yup.. I need to find   ((ration=total / present)).

 

Also I need the data steps.

 

*Refer the attached images

 

Capture.JPG


Capture1.JPG
Super User
Super User
Posts: 7,401

Re: How to calculate ratio between two variables

It looks like your data hasn't imported correctly.  How are you importing the CSV file?   The below is quite correct as present and totla are missing then ratio will be missing.  Fix importing your your data so the dataset contains values, then run the code I have given you.

Occasional Contributor
Posts: 12

Re: How to calculate ratio between two variables

You can see the script in the second image attached..

Kindly download it.
☑ This topic is SOLVED.

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

Discussion stats
  • 21 replies
  • 410 views
  • 3 likes
  • 5 in conversation