## how to use SAS macro and transpose to calculate the sum and percentage of values generated by macro

Super Contributor
Posts: 345

# how to use SAS macro and transpose to calculate the sum and percentage of values generated by macro

[ Edited ]
``````%BS(1. Child, child);
%BS(3. Senior, senior);``````

I want to use sas to calculate sum and percentage from a table generated by sas macro. Someone in this community suggested me use transpose. It is hard for me to do since the original table was generated by sas macro. I just cannot do to combine sas macro and transpose.

So I provide my sas code, and wish get advice from you

my goal is to use sas to calculate percentage and sum, so it does not have to use transpose. Anything works is fine

thanks

Super User
Posts: 23,773

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

Do you understand the macro code? I think the original suggestion was to modify the macro to generate the table of interest rather than modify the output.

Super Contributor
Posts: 345

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

[ Edited ]
`` ``

The first part of macro code is to generate six tables, for patients with ADHD, anxiety, and depression. Each disease has two tables, first year, and second year. Each table includes year from 2006-2008 and 2009. Each table also includes child, adult, and senior.
Svc1 has value 1. Child, svc2 has value child2. With this part, we can generate six tables. A sample table is attached. But I need to calculate the total number of patients, and the percentage to have a second table. That is what I am asking for. In reality, the table is complicated and has many rows and columns with many obs. So it is very time consuming to calculate one by one.

Super User
Posts: 9,599

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

Well, I don;t have any test data, so can't be acurate.  however from what you have said you have defined the groups here:

"ADHD, anxiety, and depression. Each disease has two tables, first year, and second year. Each table includes year from 2006-2008 and 2009. Each table also includes child, adult, and senior."

Now I don't know your data, but if you manipulate to look like:

...  DISEASE_CAT  YEAR   AGE_GROUP  RESULT

...

Once you have data into a workable dataset, then you can simply apply your procedures to that data, using by group processing, e.g.

```proc means data=have;
by disease_cat year age_group;
var result;
output ...;
run;```

This will give you one table out with the various groups, and is far easier and faster than trying to force it.  If you put in some test data in the form of a datastep I can clarify.

Super Contributor
Posts: 345

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

In fact, each disease has a different dataset. It is so huge so I do not analyze these together. Agegroups also have different huge datasets.

In stead, I am required to report with each different table, with each different disease category, as always.

Othe than that, my sample data is like the one I have provided.

Super User
Posts: 13,583

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

wenling wrote:

In fact, each disease has a different dataset. It is so huge so I do not analyze these together. Agegroups also have different huge datasets.

In stead, I am required to report with each different table, with each different disease category, as always.

Othe than that, my sample data is like the one I have provided.

Just how "huge" is huge?

The series of questions appears as if you might be attempting to duplicate a series of steps imposed because you were working in a spread sheet. If that is the case, then it may be time to realize that SAS is a different tool with different capabilities and limitations. A common limitation in spreadsheets is the size of workable data. The limits is SAS are vastly larger than for spreadsheets.

The results you show as "desired" are likely easier to generate from a single dataset instead of putting little pieces together especially when you are looking a percentages of combined data.

Super User
Posts: 9,599

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

Hi,

It would be easier if you post some test data, in the form of a datastep, and required output.

Super User
Posts: 23,773

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

Please post sample data as a data step, text in the question, preferably not a screenshot. Like how I generated a fully worked sample for your previous question. Especially if you want code.

Super Contributor
Posts: 345

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

[ Edited ]

Super Contributor
Posts: 345

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

[ Edited ]

Super Contributor
Posts: 345

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

I have put sample data and desired output. I do not understand you said about "dataset as your data step". I cannot put two files at one time, so I have to attached one by one

I have generated first table  and need to calculate the total number of each column, and then the percentage is the number of each cell devided by the total number

Super User
Posts: 23,773

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

wenling wrote:

I do not understand you said about "dataset as your data step".

This is an example of dataset as a data step:

``````*Create sample data;
data have;
input mygroup \$ subgroup \$ b d;
cards;
se A 371130 11243
se N 214144 2214
se M 181841 5820
se F 168065 3288
ex M 779199 17987
ex F 155981 4578
ge s20 603224 14766
ge S25 263337 5918
ge o25 68607 1881
ac W 689531 17304
ac B 159304 3714
ac Ot 86345 1547
ed BH 1766 76
ed HS 806844 19491
ed Co 74217 2014
ed B 52171 979
q 99 66736 1373
q 92 376550 8726
q 64 259887 6356
q 49 221302 6009
q 29 2635 83
s fq 811106 19056
s tq 37385 1178
s pq 86689 2331
;
run;``````

When you post data as an XLSX, or an image, you're putting the burden on getting data on the person who's trying to answer the question. This can mean downloading a file, writing a proc import, or generating fake data before even attempting to answer your question.  For faster answers, include your data as a data step.

Super Contributor
Posts: 345

## Re: how to use SAS macro and transpose to calculate the sum and percentage of values generated by ma

Thank you. I will do next time for future questions. But this time, the problem is, I want SAS to calculate the percentage and sum from tables that generated by SAS macro. I agree with your prevous post that I prefer to modify the first part of the code, rather than to have a new data step to put those numbers again, if I have to do that data step.

I would not do so, if I have to. That will be more time consuming, than simply to calculate the percentage and the sum one by one.

Any suggestions on how to modify my first part of code to calculate the percentage and the sum?

Super Contributor
Posts: 345