topic Re: How to calculate ratio between two variables in SAS Programming

How to calculate ratio between two variables

Anil9 — Thu, 09 Mar 2017 19:28:33 GMT

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.

Re: How to calculate ratio between two variables

PeterClemmensen — Thu, 09 Mar 2017 10:13:15 GMT

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

Re: How to calculate ratio between two variables

RW9 — Thu, 09 Mar 2017 10:14:57 GMT

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;

Re: How to calculate ratio between two variables

Anil9 — Thu, 09 Mar 2017 18:52:09 GMT

Thanks for the statement..

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

Re: How to calculate ratio between two variables

Anil9 — Thu, 09 Mar 2017 18:53:40 GMT

Hiii.....!

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

Re: How to calculate ratio between two variables

RW9 — Thu, 09 Mar 2017 19:40:16 GMT

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

Re: How to calculate ratio between two variables

Yavuz — Thu, 09 Mar 2017 20:06:35 GMT

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;

Re: How to calculate ratio between two variables

ballardw — Thu, 09 Mar 2017 23:37:32 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: How to calculate ratio between two variables

Anil9 — Fri, 10 Mar 2017 12:26:55 GMT

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.

Re: How to calculate ratio between two variables

RW9 — Fri, 10 Mar 2017 12:29:10 GMT

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.

Re: How to calculate ratio between two variables

Anil9 — Fri, 10 Mar 2017 12:29:46 GMT

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.

Re: How to calculate ratio between two variables

RW9 — Fri, 10 Mar 2017 12:42:56 GMT

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.

Re: How to calculate ratio between two variables

Anil9 — Fri, 10 Mar 2017 12:44:50 GMT

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

Also I need the data steps.

*Refer the attached images

Re: How to calculate ratio between two variables

RW9 — Fri, 10 Mar 2017 12:48:33 GMT

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.

Re: How to calculate ratio between two variables

Anil9 — Fri, 10 Mar 2017 12:50:31 GMT

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

Kindly download it.

Re: How to calculate ratio between two variables

RW9 — Fri, 10 Mar 2017 12:53:59 GMT

I can see nothing in the image you posted other than a photo of your screen showing a dataset with nothing in it.

Re: How to calculate ratio between two variables

Anil9 — Fri, 10 Mar 2017 12:55:05 GMT

Re: How to calculate ratio between two variables

RW9 — Fri, 10 Mar 2017 13:00:45 GMT

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.

Re: How to calculate ratio between two variables

Anil9 — Fri, 10 Mar 2017 13:04:58 GMT

Yes i agree it runs well when it is few rows (I.e 10 - 30 ) i have entered more than 13000.

Re: How to calculate ratio between two variables

RW9 — Fri, 10 Mar 2017 13:09:28 GMT

Then its likely that it is your data. Look at the first time you get a value you don't expect, and look at the data. Judging from the picture you posted, there are observations which don't have any data at all, maybe the import process creating a few blanks rows, or perhaps your CSV is corrupt in some way. Its something to do with the data anyways as the logic will happily apply the same to each data row.