BookmarkSubscribeRSS Feed
Bal23
Lapis Lazuli | Level 10
%BS(1. Child, child);
%BS(2. Adult, adult);
%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

13 REPLIES 13
Reeza
Super User

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.

Bal23
Lapis Lazuli | Level 10
 

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

...  ADHD               2001    CHILD            abd

...  ADHD               2002    CHILD            efg

...

 

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.

Bal23
Lapis Lazuli | Level 10

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.

ballardw
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

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

Reeza
Super User
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.

Bal23
Lapis Lazuli | Level 10

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

Reeza
Super User

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

Bal23
Lapis Lazuli | Level 10

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?

 

 

Bal23
Lapis Lazuli | Level 10

sorry i attached a wrong dataset, please see this one instead

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1223 views
  • 0 likes
  • 4 in conversation