BookmarkSubscribeRSS Feed
leeleelee
Calcite | Level 5

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,

1 REPLY 1
mklangley
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 320 views
  • 0 likes
  • 2 in conversation