topic Re: How to divide all the observations based on a sum of a column in New SAS User

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

heyyou1 — Tue, 16 Nov 2021 20:03:54 GMT

Hello, 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 */

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% */

3. 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

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

PaigeMiller — Tue, 16 Nov 2021 20:12:59 GMT

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

Because you told SAS that it was character by using a $ after RETURN in the INPUT statement.

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.

3. Next I want to find their ROI. Which is (return-investment) / investment * 100. This is the result I am looking for

proc sql;
    create table want as select *, return/sum(return) as share_of_return,
        (return-invested)/invested*100 as roi 
    from money;
quit;

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

sbxkoenk — Tue, 16 Nov 2021 20:18:57 GMT

Hello,

I do not do * 100 (times 100) as I am using PERCENTw.d format

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

proc means data=money0 sum nway noprint;
var return;
output out=work.abc_xyz sum= / autoname;
run;

data money1;
 set money0;
 if _N_=1 then set work.abc_xyz(drop=_:);
 share_of_return = return / return_sum;
 ROI = (return-invested) / invested;
 format share_of_return ROI percent7.2;
run;
/* end of program */

Koen