topic Re: Sum of all numeric colums from different tables( from 2 separate libraries as well) in SAS Programming

Sum of all numeric colums from different tables( from 2 separate libraries as well)

Maker10 — Tue, 25 Jul 2017 19:00:28 GMT

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!

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Reeza — Tue, 25 Jul 2017 19:06:23 GMT

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

Number them, if that helps 🙂

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Maker10 — Tue, 25 Jul 2017 19:15:03 GMT

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

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Shmuel — Tue, 25 Jul 2017 19:37:37 GMT

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?

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Maker10 — Tue, 25 Jul 2017 19:43:17 GMT

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

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Reeza — Tue, 25 Jul 2017 19:45:13 GMT

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.

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Reeza — Tue, 25 Jul 2017 19:45:51 GMT

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

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Maker10 — Tue, 25 Jul 2017 19:50:38 GMT

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!

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Reeza — Tue, 25 Jul 2017 20:00:50 GMT

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

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Shmuel — Tue, 25 Jul 2017 20:20:37 GMT

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

Re: Sum of all numeric colums from different tables( from 2 separate libraries as well)

Reeza — Tue, 25 Jul 2017 21:19:30 GMT

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.