BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

There are two tables MBR_2010Q3 and MBR_2010Q4 they have the same data structre like Country,City,State,Earnings. Now How to get the difference between Q3 and Q4 earnings grouped on country,city,state.There are 5000 records in Q3 dataset and 9000 records in Q4.

8 REPLIES 8
art297
Opal | Level 21

You could always merge the two files together, changing the name of the earnings variable in one of the files, and then simply create a difference_variable and summarize it with proc means or proc summary.

data_null__
Jade | Level 19

Do you difference then summarize or summarize then difference.

PROC COMPARE is an option you might consider.

data class0;

   set sashelp.class;

   array _n

  • _numeric_;

       do _n_ = 1 to dim(_n);

          _n[_n_] = _n[_n_] + rannor(12345);

          end;

       run;

    proc compare base=sashelp.class compare=class0 outdif out=dif;

       id name;

       var _numeric_;

       run;

    Proc print;

       run;

    Ksharp
    Super User

    Did you mean the difference of these two variables's value?

    You can use proc means to get the earning for each group,then merge them into a table to get what you want.

    Ksharp

    Peter_C
    Rhodochrosite | Level 12

    as I feel out-of-work

    and this sounds like home-work

    here is some work done at home

    1. code to generate test data,
    2. summarise totals and averages for each quarter,
    3. a compare/calculate data step which prepares a data set for
    4. proc print

    results are attached (in html format from sas9.3)

    ************************************

    MBR_2010Q3 and MBR_2010Q4 

    have the same data structre like

    Country,City,State,Earnings.

    get the difference between Q3 and Q4 earnings

    grouped on country,city,state.

    ;

    *1 generate test data;

    data MBR_2010Q3 MBR_2010Q4;

       keep   Country City State Earnings;

       length country City $20

           ;

       do country = 'Here', 'There', 'some Where Else';

        ref1+1;

           do City = 'Hope', 'glory', 'industrial', 'services';

          ref2+1;

               do state= 353, 355;

                   do t=1 to 100;

                      *to bulk up the test data;

               ref3+1;

               earnings = ranuni(ref1+ref2+ref3)*1000;

                       if ranuni(1) >.1 then output MBR_2010Q3;

               earnings = ranuni(ref1+ref2+ref3*7)*1000;

                       if ranuni(1) <.9 then output MBR_2010Q4;

                  end;

              end;

          ref3=0;

          end;

       ref2=0;

       end;

       stop;

    run;

    *2 generate totals and averages;

    proc means noprint data= MBR_2010Q3 nway;

       class  Country City State;

       var    earnings;

       output sum= total_earnings3 mean=ave_earnings3

              out= q3_stats;

    run;

    *  for the other quarter too;

    proc means noprint data= MBR_2010Q4 nway;

       class  Country City State;

       var    earnings;

       output sum= total_earnings4 mean=ave_earnings4

              out= q4_stats;

    run;

    *3 compare (merge) to measure growth;

    data cf;

       merge q3_stats q4_stats;

       by  Country City State;

       if n( ave_earnings3, ave_earnings4 )=2 then

          do;

             ave_delta =   ave_earnings4 / ave_earnings3 -1;

             sum_delta = total_earnings4/total_earnings3 -1;

             growth    = total_earnings4-total_earnings3;

          end;

       format    ave_delta sum_delta percent8.2

                ave_earnings3 ave_earnings4            comma12.2

                total_earnings3 total_earnings4 growth comma12.;

       label     ave_earnings3     ='average earnings 2010Q3'

                ave_earnings4     ='average earnings 2010Q4'

                ave_delta         ='growth in average earnings'

                total_earnings3   ='aggregate earnings 2010Q3'

                total_earnings4   ='aggregate earnings 2010Q4'

                growth         ='growth of aggregate earnings 2010Q3 to Q4'

                sum_delta ='growth rate of aggregate earnings 2010Q3 to Q4'

       ;

    run;

    *4 report measures;

    proc print label;

       title      .h=5 'earnings growth';

       footnote .h=1 "demo by peterC at %sysfunc( datetime(),twmdy)";

       id    Country City State;

       var   ave_earnings3 ave_earnings4 ave_delta

            total_earnings3 total_earnings4

            growth sum_delta;

    run;

    title; footnote;

    SASPhile
    Quartz | Level 8

    Hi Peter,

    Thanks for the reply. I'm wondering if this can be done in proc sql something like this:

      

    proc sql;
    create table diff as
      select a.*,b.earnings as earnings_q4, a.earnings-b.earnings as diff
       from Mbr_2010q3 as a full join Mbr_2010q4 as b
       on a.country=b.country and
       a.city=b.city and
       a.state=b.state;

    quit;

    art297
    Opal | Level 21

    I didn't test your code but it in essence is doing what all of the suggestions have been: merge the files and obtain the difference between the earnings.  Except that you are just creating the merged file.  Why stop there?  You could easily add the summary function to get just one record containing the sums for each group (of course you would have to add in the distinct and group by clauses).

    SASPhile
    Quartz | Level 8

    Art,

    It doesnt give me the expected result as the join results in 191637 records.

    art297
    Opal | Level 21

    Doesn't the following approximate what you are trying to do?

    /*create some test datasets*/

    data mbr_2010q3 (keep=country city state earnings);

      set sashelp.class (rename=(

        sex=country height=earnings));

      if substr(name,1,1) lt 'N' then city=1;

      else city=2;

      if age lt 15 then state=1;

      else state=2;

      earnings=earnings+weight;

    run;

    data mbr_2010q4 (keep=country city state earnings);

      set sashelp.class (rename=(

        sex=country height=earnings));

      if substr(name,1,1) lt 'N' then city=1;

      else city=2;

      if age lt 15 then state=1;

      else state=2;

    run;

    proc sql;

      create table diff as

        select a.country,a.city,

          sum(a.earnings)-sum(b.earnings) as diff

            from Mbr_2010q3 as a full join Mbr_2010q4 as b

              on a.country=b.country and

                   a.city=b.city and

                   a.state=b.state

                     group by a.country,a.city

      ;

    quit;

    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
    • 8 replies
    • 978 views
    • 0 likes
    • 5 in conversation