How to find total in the last column

[Amy0223]

Hello, 

Could someone please advise how to get the total in the last column? I really appreciate your kind help!

[jimbarbour]

Is that Excel?  If that is Excel, it might be better to post this question on an Excel forum.

If you have an underlying SAS dataset, please post the first dozen or so rows of data.  If you have a SAS program that is producing the Excel spreadsheet, please post the source code.

Jim

[Amy0223]

This is SAS but I just wanted to show what the SAS output might looks like using Excel. Sorry, I don't have the source code. The row number should start as 1 from above xx mg. It's row 1 to 7 only. Any suggestions is greatly appreciated.

[PaigeMiller]

---

@Amy0223 wrote:

Hello, 

Could someone please advise how to get the total in the last column? I really appreciate your kind help!

---

I'm afraid the request to compute a TOTAL doesn't make any sense here. You have text strings in a2, a3, etc. How could you add the text strings "1(50.0%)" + "1(50.0%) + "1(20.3%)" + ... ??? Please explain further what should go into the TOTAL column.

If your SAS data set really (as shown) contains text strings like "1(50.0%)", this is a particularly poor way to represent and store data for the purposes of creating a total (or for any other purpose I can think of)

[smantha]

data new;
set old;
array temp a2 a3 a4 a5 a6;
array temp_n(5) ;
total=0;
do i = 1 to dim(temp);
if index(temp[i],'(') > 0 then do;

   total+=input(scan(scan(temp[i],2,'('),-1,')'),percent4.2);

end;

else do;

  total+= 0;

end;
end;
run;

As Paige suggested the format is not correct for columns a2 a3 etc.. However hope the above helps.

[ballardw]

---

@Amy0223 wrote:

Hello, 

Could someone please advise how to get the total in the last column? I really appreciate your kind help!

---

Just what would that "total" actually look like for the example values? Summing the percentages for A2 through A6 sounds most likely to be way off.

[Amy0223]

The total includes total counts and percentages for A2 through A6. Thank you!

[PaigeMiller]

---

@Amy0223 wrote:
The total includes total counts and percentages for A2 through A6. Thank you!

---

Just add the counts and the percentages? Does that make any sense? I don't think so, you can't add percentages, it won't make any sense, if I am understanding the numbers.

Can you show us what you want by filling in the cells with the desired values?

[Amy0223]

I think there is a big N that was left out, the percentages are based on total counts over big N( n=30). The counts should be add up to the total. The first total for >=400 mg is 6(6/30) which is 6(20.0%)
Sorry for the confusion.

[Kurt_Bremser]

Before we waste any more time spinning fairy tales, post an example for your source dataset in usable form (data step with datalines), so we can be sure about types, content and formats, and have something to test code against. Post your code in a code box. Also show us the numbers you want to get out of that example data.

[PaigeMiller]

 As stated above, you have picked a very poor way to store the data, making it actually harder to get the results you want. You have to pull apart character strings, change the resulting character strings to numeric, and then add. Nevertheless, here is some UNTESTED code for you, since we can't work from screen captures (if you want tested code, we'd need the actual data as a SAS data step).

data want;
    set have;
    array a{*} a2-a6;
    total=0;
    do i=1 to dim(a);
        total = sum(total, input(scan(a(i),1,'('),2.));
    end;
    total=total/30;
    drop i;
run;

If the above code works, please don't simply say it works and leave. Please take to heart the advice you have been given not to store numbers in complicated text strings, so that next time you are in this situation, the process of addition is much simpler.