Join tables to compare numbers, then export

leeleelee — Tue, 26 Oct 2021 19:06:22 GMT

I have 3 Tables.

Table_Example is in a cloud and I am running it as an output with the code:

libname Example "location";

Data tst;
set Example.filename;
run;

I have tables Table_A and Table_B in my local folder which are in sas output formats.

How do I join all three of these tables to give me an output like the excel file below?

I am looking for Counts from Example table, and am comparing Table_A and Table_B values to the Example table to see if those numbers will match to the Example table. If they do not match, then I would like the cell values to return: "Yes" or "No"

(Note: the sheet tabs do not need to be named like the example)

Thank you in advance,

Re: Join tables to compare numbers, then export

mklangley — Tue, 26 Oct 2021 20:01:19 GMT

It's always easiest if you provide your input data in DATA steps (with runnable SAS code), instead of in an attached Excel file. That would save a lot of time for anything trying to answer your question.

That being said, try this:

/* Input tables: Example, Table_A, Table_B */
data example;
    input month $ example_a_people example_b_people 
          example_a_cost :dollar18.2 example_b_cost :dollar18.2;
    format example_a_people example_b_people comma18.
           example_a_cost example_b_cost dollar18.2;
    datalines;
    JANUARY 891261 755647 $364,887,089.49 $444,416,822.15 
    FEBRUARY 890676 754530 $371,475,087.93 $412,213,242.89 
    MARCH 891065 754209 $476,003,486.18 $440,867,349.40 
    APRIL 890138 752780 $456,330,085.56 $430,830,371.82 
    MAY 890843 752664 $422,915,914.71 $402,640,043.83 
    JUNE 12345 67890 $123,456,789.00 $987,654,321.00
    ;
run;

data table_a;
    input month $ table_a_people table_a_cost :dollar18.2;
    format table_a_people comma18.
           table_a_cost dollar18.2;
    datalines;
    JANUARY 891180 $446,993,253.08 
    FEBRUARY 890601 $415,152,551.44 
    MARCH 890990 $445,130,842.18 
    APRIL 890064 $436,034,759.94 
    MAY 890758 $415,535,218.42 
    JUNE 12345 $123,456,789.00
    ;
run;

data table_b;
    input month $ table_b_people table_b_cost :dollar18.2;
    format table_b_people comma18.
           table_b_cost dollar18.2;
    datalines;
    JANUARY 755618 $367,809,378.34 
    FEBRUARY 754498 $374,460,988.79 
    MARCH 754183 $480,843,379.54 
    APRIL 752753 $462,962,212.25 
    MAY 752629 $433,581,101.59 
    JUNE 67890 $987,654,321.00
    ;
run;

/* Compare People */
proc sql;
    create table compare_people as
    select e.month
          ,e.example_a_people
          ,e.example_b_people
          ,a.table_a_people
          ,b.table_b_people
          ,case
               when e.example_a_people = a.table_a_people
                   then 'YES'
               else 'NO'
           end as difference_a_people
          ,case
               when e.example_b_people = b.table_b_people
                   then 'YES'
               else 'NO'
           end as difference_b_people
    from example e
    left join table_a a
        on e.month = a.month
    left join table_b b
        on e.month = b.month
    order by month(input('01'||substr(month,1,3)||'2021',date9.))
    ;
quit;

/* Compare Cost */
proc sql;
    create table compare_cost as
    select e.month
          ,e.example_a_cost
          ,e.example_b_cost
          ,a.table_a_cost
          ,b.table_b_cost
          ,case
               when e.example_a_cost = a.table_a_cost
                   then 'YES'
               else 'NO'
           end as difference_a_cost
          ,case
               when e.example_b_cost = b.table_b_cost
                   then 'YES'
               else 'NO'
           end as difference_b_cost
    from example e
    left join table_a a
        on e.month = a.month
    left join table_b b
        on e.month = b.month
    order by month(input('01'||substr(month,1,3)||'2021',date9.))
    ;
quit;

/* Export results to separate tabs of a single Excel file */
proc export data=compare_people
    outfile="my_path/output_file.xlsx"
    replace
    dbms=xlsx;
    sheet='People'
    ;
quit;

proc export data=compare_cost
    outfile="my_path/output_file.xlsx"
    replace
    dbms=xlsx;
    sheet='Cost'
    ;
quit;

topic Join tables to compare numbers, then export in SAS Programming

Join tables to compare numbers, then export

Re: Join tables to compare numbers, then export