How to divide all the observations based on a sum of a column

I’m trying to do simple calculations but I’m new and SAS is not intuitive to me.

Suppose I have this table.

data money;
infile datalines delimiter=",";
input name $ return $ invested;
datalines;
Joe,10,100
Bob,7,50
Mary,80,1000
;

Which creates this

/* name | return | invested */
/* _________________________ */
/* Joe  | 10     | 100 */
/* Bob  | 7      | 50 */
/* Mary | 80     | 50 */

I have three things I would like to do for my job that just switched over to SAS.

  1. I need to make sure columns return and invested are numeric. When I run the code above, return column ends up being a CHAR column and I don’t know why.

  2. Now I want to create a new column and calculate the share of the total return they each got. In this case, the sum of return=97. This is the result I want.

/* name     | return | invested | share_of_return */
/* ____________________________________________ */
/* Joe      |  10   | 100       | 10.30% */
/* Bob      |   7   | 50        | 7.22% */
/* Mary     | 80    | 50        | 82.47% */
  1. Next I want to find their ROI. Which is (return-investment) / investment * 100. This is the result I am looking for
/* Find ROI */

/* name | return | invested | share_of_return | ROI */
/* ___________________________________________________ */
/* Joe  | 10     | 100      | 10.30%          | -90% */
/* Bob  | 7      | 50       | 7.22%           | -86% */
/* Mary | 80     | 50       | 82.47%          | 60% */

I appreciate your explanations and guidance in advanced. This is for a work project and we just switched over to SAS

>Solution :

1 & 3 are easy, 2 is slightly more difficult.

  1. Remove $ in INPUT statement. $ indicates character. In your data you may need to convert it using the input function instead though.

Fix for example:

input name $ return  invested;

Fix for actual data using input function. Note that you cannot convert types in a data step to the same name so I rename it while reading it in using the rename data set option.

data money2;
set money (rename = return = return_char);
return = input(return_char, best.);
drop return_char;
run;
  1. Add total value to data step, SQL is fastest here:
proc sql;
create table money3 as
select *, sum(return) as return_total, return/calculated return_total as return_percentage f=percent12.1
from money2;
quit;

I outline two different methods of doing this here

  1. Within a data step, add your calculation. It’s probably most efficient if it can be done in first step.
    Since a data step loops automatically you write the formula pretty much as shown. In this case I’ve also applied a format so it shows as a percentage but that requires you to not multiply it by 100. Depending on what you’re doing next it may be best to leave it as numeric.
data money2;
set money (rename = return = return_char);
return = input(return_char, best.);
ROI = (return - investment)/investment;
format ROI percent12.1;
run;
drop return_char;
run;

Leave a Reply