DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 4

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

Hello mates Smiley Happy

 

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!

Super User
Posts: 19,770

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

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

Number them, if that helps Smiley Happy

New Contributor
Posts: 4

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

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

Trusted Advisor
Posts: 1,554

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

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?

New Contributor
Posts: 4

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

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 Smiley Sad 

6. I want the report to be a SAS Table

Super User
Posts: 19,770

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

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

Super User
Posts: 19,770

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

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. 

 

 

New Contributor
Posts: 4

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

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!

Super User
Posts: 19,770

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

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

Trusted Advisor
Posts: 1,554

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

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. ...
Super User
Posts: 19,770

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

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. 

Ask a Question
Discussion stats
  • 10 replies
  • 138 views
  • 1 like
  • 3 in conversation