[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Difference is sales for store1-store2 and store3-store2
From:       Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date:       2017-02-24 18:08:12
Message-ID: 1590957552087110.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]

Difference is sales for store1-store2 and store3-store2

Nicely layed out question

Other solutions could use lag or 'by not sorted'
No need to transpose.
My solution will stop processing if you have more then three 'stores' .

inspired by
https://goo.gl/OgQQNz
https://communities.sas.com/t5/Base-SAS-Programming/Is-there-any-way-to-transpose-the-observation-into-variable/m-p/335664


HAVE
====

Up to 40 obs WORK.HAVE total obs=12

Obs      DATE       DEPARTMENT    SALE

  1    30NOV2016      WEST         175
  2    31DEC2016      WEST         134
  3    31JAN2017      WEST         124

  4    30NOV2016      EAST         198
  5    31DEC2016      EAST         182
  6    31JAN2017      EAST         117

  7    30NOV2016      SOUTH        200
  8    31DEC2016      SOUTH        203
  9    31JAN2017      SOUTH        205

 10    30NOV2016      NORTH        199
 11    31DEC2016      NORTH        194
 12    31JAN2017      NORTH        106


WANT
====

Up to 40 obs from want total obs=4

              SALE_    SALE_
DEPARTMENT    DIFF1    DIFF2

  WEST         -41      -10
  EAST         -16      -65
  SOUTH          3        2
  NORTH         -5      -88

DETAILS
=======
                                                ????
Obs    DEPARTMENT    DIF1  Sale1-Sale2  DIF2  Sale3-Sale2

 1       WEST          41  175-134      -10    124-134
 2       EAST          16  198-182      -65    117-182
 3       SOUTH         -3  200-203        2    205-203
 4       NORTH          5  199-194      -88    106-194

WORKING CODE
===========

     select;
       when  (mod(_n_,3)=2) vals[1]=sale;
       when  (mod(_n_,3)=1) vals[2]=sale;
       when  (mod(_n_,3)=0) do;
           dif1=vals[2]-vals[1];
           dif2=sale-vals[1];
           output;
       end;
     end; * leave off otherwise to force error;


FULL SOLUTION
=============

DATA have;
INPUT DATE $ 1-9 DEPARTMENT $ SALE ;
CARDS;
30NOV2016 WEST 175
31DEC2016 WEST 134
31JAN2017 WEST 124
30NOV2016 EAST 198
31DEC2016 EAST 182
31JAN2017 EAST 117
30NOV2016 SOUTH 200
31DEC2016 SOUTH 203
31JAN2017 SOUTH 205
30NOV2016 NORTH 199
31DEC2016 NORTH 194
31JAN2017 NORTH 106
;
RUN;

data want;
  array vals[2] _temporary_;
  set have;
  select;
    when  (mod(_n_,3)=2) vals[1]=sale;
    when  (mod(_n_,3)=1) vals[2]=sale;
    when  (mod(_n_,3)=0) do;
        dif1=vals[2]-vals[1];
        dif2=sale-vals[1];
        output;
    end;
  end; * leave off otherwise to force error;
  keep department dif:;
run;quit;

Up to 40 obs from want total obs=4

Obs    DEPARTMENT    DIF1    DIF2

 1       WEST          41     -10
 2       EAST          16     -65
 3       SOUTH         -3       2
 4       NORTH          5     -88


[prev in list] [next in list] [prev in thread] [next in thread]