How to calculate ratio between two variables

[Anil9]

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.

[PeterClemmensen]

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? 🙂

[Anil9]

Thanks for the statement..

My data is consists of 100+ values and I need to find the ratio of it.

[RW9]

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;

[Anil9]

Hiii.....!

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

[RW9]

---

@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? 

[Anil9]

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.

[RW9]

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.

[Yavuz]

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;

[ballardw]

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.

[Anil9]

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. 

[RW9]

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.

[Anil9]

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

Also I need the data steps.

*Refer the attached images

[RW9]

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.

[Anil9]

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

Kindly download it.