topic Re: How to find total in the last column in SAS Programming

How to find total in the last column

Amy0223 — Tue, 22 Sep 2020 02:57:11 GMT

Hello,

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

Re: How to find total in the last column

jimbarbour — Tue, 22 Sep 2020 03:30:44 GMT

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

Re: How to find total in the last column

Amy0223 — Tue, 22 Sep 2020 14:20:42 GMT

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.

Re: How to find total in the last column

PaigeMiller — Tue, 22 Sep 2020 14:36:51 GMT

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

Re: How to find total in the last column

smantha — Tue, 22 Sep 2020 15:05:49 GMT

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.

Re: How to find total in the last column

ballardw — Tue, 22 Sep 2020 15:34:53 GMT

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

Re: How to find total in the last column

Amy0223 — Tue, 22 Sep 2020 16:08:22 GMT

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

Re: How to find total in the last column

PaigeMiller — Tue, 22 Sep 2020 16:11:57 GMT

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

Re: How to find total in the last column

Amy0223 — Tue, 22 Sep 2020 16:21:09 GMT

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.

Re: How to find total in the last column

Kurt_Bremser — Tue, 22 Sep 2020 16:34:05 GMT

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.

Re: How to find total in the last column

PaigeMiller — Tue, 22 Sep 2020 16:40:47 GMT

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.