07-25-2017 03:00 PM
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!
07-25-2017 03:06 PM
Can you lay out the steps you need to do in clear order please?
Number them, if that helps
07-25-2017 03:15 PM
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
07-25-2017 03:37 PM
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?
07-25-2017 03:43 PM
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
07-25-2017 03:45 PM
If the numbers get too large with the sum you're going to have numerical precision issues.
07-25-2017 03:45 PM
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.
07-25-2017 03:50 PM
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!
07-25-2017 04:20 PM
Here is a macro code to sum all numeric variables of a given table:
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;
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;
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. ...
07-25-2017 05:19 PM
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.