BookmarkSubscribeRSS Feed
Maker10
Calcite | Level 5

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!

10 REPLIES 10
Reeza
Super User

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

Number them, if that helps 🙂

Maker10
Calcite | Level 5

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

Shmuel
Garnet | Level 18

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?

Maker10
Calcite | Level 5

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

Reeza
Super User

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

Reeza
Super User

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. 

 

 

Maker10
Calcite | Level 5

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!

Reeza
Super User

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

Shmuel
Garnet | Level 18

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. ...
Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1771 views
  • 1 like
  • 3 in conversation