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
- /
- Base SAS Programming
- /
- Sum of all numeric colums from different tables( f...

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

4 weeks ago

Hello mates

Currently I am trying to develop a SAS Data Set that sums all the numeric columns from different tables.

Those tables are part of 2 separate libraries as well.

Finally, I have to compare them.

One of the library(for example "da") contains all the input tables for the other library(for example "dada").

So I need to make sure that for each table from the input library("da") the sum is equal with the sum from the target table(from "dada").

I am a beginner so I need help with this script.

Thank you for your support!

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

4 weeks ago

Can you lay out the steps you need to do in clear order please?

Number them, if that helps

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

3 weeks ago

Thank you for your response!

1. I need to sum all the numeric columns from the tables in the target library ("da")

2. I need to sum all the numeric columns from the tables in the input library ("dada")

3. I need to check which columns from the library "dada" are input for the columns from library "da"

4. Based on that check I need to compare the sums from the input columns from library "dada" with the sums from the target tables from the library "da"

5. Deliver the report

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

3 weeks ago

Are all tables of the same format?

Numeric variables can contain dates? Do you wan to sum them also?

Some code variables also may be numeric. Do you wan to sum them also?

Do yo want to compare each variable separately? or the total sum per library?

What are the names of the tables? do they have same format or convention?

What report format do you expect?

What do you expect? A method or a code to do it?

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

3 weeks ago

Thank you for your reply!

1. No, the tables do not have the same format

2. Yes, they contain dates and I want to sum them also

3. I want to sum the code variables also.

4. I want to compare the total sum per table ( input table compared to target table)

5. I can't divulge the name of the tables unfortunatelly

6. I want the report to be a SAS Table

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

3 weeks ago

If the numbers get too large with the sum you're going to have numerical precision issues.

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

3 weeks ago

Step 3 is vague, you'll have to show how that should work.

If the tables are unique and you can't stack them, you have to loop through all, which is a pain of course.

1. Get a list of tables in library da/dada -> use sashelp.vtable for this

2. Create a macro that creates your summary stats and appends them to a master table.

3. Use call execute to call macro based on data from #1. See Call Execute documentation for an example.

4. Merge or proc compare the outputs required.

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

3 weeks ago

Thank you!

How can I loop through all? Because this will ease my work. I wanted to call the macro "n" times with all the names of the tables from the 2 libraries.

If i could loop somehow through them, it would be awesome!

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

3 weeks ago

See Step #3 - do not loop, use call execute.

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

3 weeks ago

Here is a macro code to sum all numeric variables of a given table:

`%macro sum(dsnin,dsnout); `

data &dsnout; /* dsnout isdataset name contaning the total */
set &dsnin end=eof; /* dsnin is either da.xxx or dada.xxx where xxx is any given dataset name */
retain total 0;
array nums _numeric_;
do i=1 to dim(nums);
tot = sum(of tot, nums(i));
end;
total = sum(of toatl, tot);
if eof then output;
keep total;
run;

%mend sum;

next is a macro to sum and compare the totals of two comparable tables, using above macro:

```
%macro rep(da_table, dada_table);
%sum(&da_table, sum_da);
%sum(&dada_table, sum_dada);
data totals;
merge sum_da (rename=(total = da_sum))
sum_dada(rename=(total=dad_sum));
by;
table_da = "&da_table";
table_dada = "&dada_table";
run;
proc append base=report data=totals; /* assuming report is the dataset name to contain the report */
run;
```

%mend rep;

finally run next code to get the report as dataset :

```
proc dataset lib=work nolist; delete report; run; quit;
%rep(...1st pair of datasets...);
%rep(...2nd pair of datsets...);
... etc. ...
```

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

3 weeks ago

I assumed the requirement was to sum each variable individually...is it each variable or all numerics into one sum? If so, and you're trying to ensure the file is the same there are better ways.