turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Help: Try to sum up columns with dynamic count

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-08-2013 09:08 AM

Hello,

I have a data set with a dynamic number of columns. This means the count of the columns is growing every trimester by 1.

Now I should be able to add a additional line which is calculating the total (sum).

Does anybody have an idea?

The set looks like the following:

X_Class1 | 2012/1 | 2012/2 | 2012/3 | 2012/4 |
---|---|---|---|---|

1 | 500 | 600 | 300 | 1000 |

2 | 400 | 550 | 250 | 850 |

3 | 100 | 200 | 110 | 620 |

Now I should generate a line X_Class1:9999 with the totals of each column in it.

In addition at the 4/1/13 an new column 2013/1 will be added and the total must also be generatet automatically in there.

thank you

Accepted Solutions

Solution

01-09-2013
09:07 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to meatmuffin

01-09-2013 09:07 AM

My earlier solution should give you exactly the numbers you asked for. It won't say "Total" because that wasn't part of the original problem, but that's easy to accomplish by making three changes:

where X_Class1 ne 'Total';

(where=(X_Class1 ne 'Total'))

X_Class1='Total';

Notice that ne : has become ne. If you're getting output that is not what you want, post what changes you would like to see.

Good luck.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to meatmuffin

01-08-2013 10:00 AM

In your SAS data set name the dynamic colums with a common root. QTR2012_1 QTR2012_2 etc.

Then you can use a "SAS Variable List" such as QTR: to refer to all the variables in a consise way.

TOTAL=sum(OF qtr; The keyword OF is important for use in the function.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to meatmuffin

01-08-2013 10:48 AM

You're leaving a lot to the imagination here. I'll assume (as data _null_ did) that your numeric variable names all begin with QTR. Also, I'll assume that X_Class1 is character with a length of at least 6. Also, I'll assume your data set doesn't contain any additional variables that you haven't told us about.

Here's an approach:

proc means data=have noprint;

var qtr:;

where X_Class1 ne : '1:';

output out=totals (keep=qtr mean=;

run;

data want;

set have (where=(X_Class1 ne : '1:')) end=done;

output;

if done;

set totals;

X_Class1=cats('1:', _n_);

output;

run;

Good luck.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to meatmuffin

01-09-2013 04:53 AM

Thanks for the answers, but it didn't give me the result i expected.

I think I was not precisely enough. I'm sorry for that.

I try it once again.

At the moment I'm having the table as shown above by my first posting.

After summarizing the data, i would like to get the following table:

XClass_1 | 2012/1 | 2012/2 | 2012/3 | 2012/4 | 2013/1 |

1 | 500 | 600 | 300 | 1000 | 500 |

2 | 400 | 550 | 250 | 850 | 300 |

3 | 100 | 200 | 110 | 620 | 200 |

Total | 1000 | 1350 | 660 | 2470 | 1000 |

After summarizing the trimester the red line with the totals should be generated.

Thanks for the help.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to meatmuffin

01-09-2013 08:40 AM

data have;

input A $ B1 b2 b3 b4 b5;

data have;

input A $ B1 b2 b3 b4 b5;

cards;

1 500 600 300 1000 500

2 400 550 250 850 300

3 100 200 110 620 200

;

data want;

set have end=last;

output;

t1+b1;

t2+b2;

t3+b3;

t4+b4;

t5+b5;

if last then do;

b1=t1; b2=t2; b3=t3;b4=t4;b5=t5;a='Total';

output;end;

drop t:;

proc print noobs;run;

A B1 b2 b3 b4 b5

1 500 600 300 1000 500

2 400 550 250 850 300

3 100 200 110 620 200

Total 1000 1350 660 2470 1000

Solution

01-09-2013
09:07 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to meatmuffin

01-09-2013 09:07 AM

My earlier solution should give you exactly the numbers you asked for. It won't say "Total" because that wasn't part of the original problem, but that's easy to accomplish by making three changes:

where X_Class1 ne 'Total';

(where=(X_Class1 ne 'Total'))

X_Class1='Total';

Notice that ne : has become ne. If you're getting output that is not what you want, post what changes you would like to see.

Good luck.