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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

21 REPLIES 21
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6
Thanks for the statement..

My data is consists of 100+ values and I need to find the ratio of it.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6
Hiii.....!

My data consists of 100+ values and we need to find the ratio of mentioned 2 columns
RW9
Diamond | Level 26 RW9
Diamond | Level 26

@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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
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
Super User

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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

 

Also I need the data steps.

 

*Refer the attached images

 

Capture.JPG


Capture1.JPG
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6
You can see the script in the second image attached..

Kindly download it.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 21 replies
  • 14953 views
  • 5 likes
  • 5 in conversation