Help using Base SAS procedures

Comparing data during the same period as present year

Reply
Contributor
Posts: 32

Comparing data during the same period as present year

Dear SAS experts,

I am wondering if you could help me with this. I am looking for a way to compared the data available for this year with the same period as the previous year?

What I would like to see is:

That the sum of data for 2010/2011 (column 6)  compares with 2011/12, until its latest available data. My dates goes from Oct-Sep, so it is possible to use also column7 (Oct10 vs. Oct11). This way I could compare the growth during the same period, in this case Oct-Jul of 2010/2011 vs. Oct-Jul of 2011/12.

2010/20112011/2012
data1250700521500
data262675130375

This is the example data:

column1 column2 column3 column4 column5 column6 column7

data1 Oct-10 product1 action1 15000 2010/2011 01-Oct-10

data2 Oct-10 product1 action1 3750 2010/2011 01-Oct-10

data1 Nov-10 product1 action1 16500 2010/2011 01-Oct-10

data2 Nov-10 product1 action1 4125 2010/2011 01-Oct-10

data1 Dec-10 product1 action1 34000 2010/2011 01-Oct-10

data2 Dec-10 product1 action1 8500 2010/2011 01-Oct-10

data1 Jan-11 product1 action1 35500 2010/2011 01-Oct-10

data2 Jan-11 product1 action1 8875 2010/2011 01-Oct-10

data1 Feb-11 product1 action1 50000 2010/2011 01-Oct-10

data2 Feb-11 product1 action1 12500 2010/2011 01-Oct-10

data1 Mar-11 product1 action1 51500 2010/2011 01-Oct-10

data2 Mar-11 product1 action1 12875 2010/2011 01-Oct-10

data1 Apr-11 product1 action1 9800 2010/2011 01-Oct-10

data2 Apr-11 product1 action1 2450 2010/2011 01-Oct-10

data1 May-11 product1 action1 11300 2010/2011 01-Oct-10

data2 May-11 product1 action1 2825 2010/2011 01-Oct-10

data1 Jun-11 product1 action1 12800 2010/2011 01-Oct-10

data2 Jun-11 product1 action1 3200 2010/2011 01-Oct-10

data1 Jul-11 product1 action1 14300 2010/2011 01-Oct-10

data2 Jul-11 product1 action1 3575 2010/2011 01-Oct-10

data1 Aug-11 product1 action1 58000 2010/2011 01-Oct-10

data2 Aug-11 product1 action1 14500 2010/2011 01-Oct-10

data1 Sep-11 product1 action1 59500 2010/2011 01-Oct-10

data2 Sep-11 product1 action1 14875 2010/2011 01-Oct-10

data1 Oct-11 product1 action1 61000 2011/2012 01-Oct-11

data2 Oct-11 product1 action1 15250 2011/2012 01-Oct-11

data1 Nov-11 product1 action1 62500 2011/2012 01-Oct-11

data2 Nov-11 product1 action1 15625 2011/2012 01-Oct-11

data1 Dec-11 product1 action1 40000 2011/2012 01-Oct-11

data2 Dec-11 product1 action1 10000 2011/2012 01-Oct-11

data1 Jan-12 product1 action1 41500 2011/2012 01-Oct-11

data2 Jan-12 product1 action1 10375 2011/2012 01-Oct-11

data1 Feb-12 product1 action1 43000 2011/2012 01-Oct-11

data2 Feb-12 product1 action1 10750 2011/2012 01-Oct-11

data1 Mar-12 product1 action1 44500 2011/2012 01-Oct-11

data2 Mar-12 product1 action1 11125 2011/2012 01-Oct-11

data1 Apr-12 product1 action1 55000 2011/2012 01-Oct-11

data2 Apr-12 product1 action1 13750 2011/2012 01-Oct-11

data1 May-12 product1 action1 56500 2011/2012 01-Oct-11

data2 May-12 product1 action1 14125 2011/2012 01-Oct-11

data1 Jun-12 product1 action1 58000 2011/2012 01-Oct-11

data2 Jun-12 product1 action1 14500 2011/2012 01-Oct-11

data1 Jul-12 product1 action1 59500 2011/2012 01-Oct-11

data2 Jul-12 product1 action1 14875 2011/2012 01-Oct-11

Thanks for your help, it is really appreciated.

Super Contributor
Posts: 1,636

Re: Comparing data during the same period as present year

Hi Coba,

Is this helpful?

data have;

infile cards dlm=' ';

informat column6 column7 $9.;

input column1 $ column2 $ column3$ column4$ column5 column6$ :column7 :& $;

cards;

data1 Oct-10 product1 action1 15000 2010/2011 01-Oct-10

data2 Oct-10 product1 action1 3750 2010/2011 01-Oct-10

data1 Nov-10 product1 action1 16500 2010/2011 01-Oct-10

data2 Nov-10 product1 action1 4125 2010/2011 01-Oct-10

data1 Dec-10 product1 action1 34000 2010/2011 01-Oct-10

data2 Dec-10 product1 action1 8500 2010/2011 01-Oct-10

data1 Jan-11 product1 action1 35500 2010/2011 01-Oct-10

data2 Jan-11 product1 action1 8875 2010/2011 01-Oct-10

data1 Feb-11 product1 action1 50000 2010/2011 01-Oct-10

data2 Feb-11 product1 action1 12500 2010/2011 01-Oct-10

data1 Mar-11 product1 action1 51500 2010/2011 01-Oct-10

data2 Mar-11 product1 action1 12875 2010/2011 01-Oct-10

data1 Apr-11 product1 action1 9800 2010/2011 01-Oct-10

data2 Apr-11 product1 action1 2450 2010/2011 01-Oct-10

data1 May-11 product1 action1 11300 2010/2011 01-Oct-10

data2 May-11 product1 action1 2825 2010/2011 01-Oct-10

data1 Jun-11 product1 action1 12800 2010/2011 01-Oct-10

data2 Jun-11 product1 action1 3200 2010/2011 01-Oct-10

data1 Jul-11 product1 action1 14300 2010/2011 01-Oct-10

data2 Jul-11 product1 action1 3575 2010/2011 01-Oct-10

data1 Aug-11 product1 action1 58000 2010/2011 01-Oct-10

data2 Aug-11 product1 action1 14500 2010/2011 01-Oct-10

data1 Sep-11 product1 action1 59500 2010/2011 01-Oct-10

data2 Sep-11 product1 action1 14875 2010/2011 01-Oct-10

data1 Oct-11 product1 action1 61000 2011/2012 01-Oct-11

data2 Oct-11 product1 action1 15250 2011/2012 01-Oct-11

data1 Nov-11 product1 action1 62500 2011/2012 01-Oct-11

data2 Nov-11 product1 action1 15625 2011/2012 01-Oct-11

data1 Dec-11 product1 action1 40000 2011/2012 01-Oct-11

data2 Dec-11 product1 action1 10000 2011/2012 01-Oct-11

data1 Jan-12 product1 action1 41500 2011/2012 01-Oct-11

data2 Jan-12 product1 action1 10375 2011/2012 01-Oct-11

data1 Feb-12 product1 action1 43000 2011/2012 01-Oct-11

data2 Feb-12 product1 action1 10750 2011/2012 01-Oct-11

data1 Mar-12 product1 action1 44500 2011/2012 01-Oct-11

data2 Mar-12 product1 action1 11125 2011/2012 01-Oct-11

data1 Apr-12 product1 action1 55000 2011/2012 01-Oct-11

data2 Apr-12 product1 action1 13750 2011/2012 01-Oct-11

data1 May-12 product1 action1 56500 2011/2012 01-Oct-11

data2 May-12 product1 action1 14125 2011/2012 01-Oct-11

data1 Jun-12 product1 action1 58000 2011/2012 01-Oct-11

data2 Jun-12 product1 action1 14500 2011/2012 01-Oct-11

data1 Jul-12 product1 action1 59500 2011/2012 01-Oct-11

data2 Jul-12 product1 action1 14875 2011/2012 01-Oct-11

;

options nocenter;

data want (drop=column2 c2 column7);

   length period $ 20;

  set have (obs=22 rename=(column5=previous_data));

  set have(firstobs=25  keep=column5 column2 rename=(column2=c2 column5=current_data));

  period=column2||'and '||c2;

  growth=(current_data-previous_data)/previous_data;

  run;

  proc print;

run;

                                                                 previous_   current_

  Obs        period       column6    column1  column3   column4     data      data     growth

  1   Oct-10  and Oct-11  2010/2011   data1   product1  action1    15000      61000    3.06667

  2   Oct-10  and Oct-11  2010/2011   data2   product1  action1     3750      15250    3.06667

  3   Nov-10  and Nov-11  2010/2011   data1   product1  action1    16500      62500    2.78788

  4   Nov-10  and Nov-11  2010/2011   data2   product1  action1     4125      15625    2.78788

  5   Dec-10  and Dec-11  2010/2011   data1   product1  action1    34000      40000    0.17647

  6   Dec-10  and Dec-11  2010/2011   data2   product1  action1     8500      10000    0.17647

  7   Jan-11  and Jan-12  2010/2011   data1   product1  action1    35500      41500    0.16901

  8   Jan-11  and Jan-12  2010/2011   data2   product1  action1     8875      10375    0.16901

  9   Feb-11  and Feb-12  2010/2011   data1   product1  action1    50000      43000   -0.14000

10   Feb-11  and Feb-12  2010/2011   data2   product1  action1    12500      10750   -0.14000

11   Mar-11  and Mar-12  2010/2011   data1   product1  action1    51500      44500   -0.13592

12   Mar-11  and Mar-12  2010/2011   data2   product1  action1    12875      11125   -0.13592

13   Apr-11  and Apr-12  2010/2011   data1   product1  action1     9800      55000    4.61224

14   Apr-11  and Apr-12  2010/2011   data2   product1  action1     2450      13750    4.61224

15   May-11  and May-12  2010/2011   data1   product1  action1    11300      56500    4.00000

16   May-11  and May-12  2010/2011   data2   product1  action1     2825      14125    4.00000

17   Jun-11  and Jun-12  2010/2011   data1   product1  action1    12800      58000    3.53125

18   Jun-11  and Jun-12  2010/2011   data2   product1  action1     3200      14500    3.53125

19   Jul-11  and Jul-12  2010/2011   data1   product1  action1    14300      59500    3.16084

20   Jul-11  and Jul-12  2010/2011   data2   product1  action1     3575      14875    3.16084

Linlin

Contributor
Posts: 32

Comparing data during the same period as present year

Thanks however,

with this two yeras I got the right results. However if I have more years, how do you manage with previous data.

I thought first convert the date to my Oct-Sep year,

but I do not manage to only compared Oct-Jul in this case. do you have a better idea?

I added column8 to the data and it is "Jul12" and imported this. sorry I can't add a file in this response

PROC IMPORT OUT= WORK.new
            DATAFILE= "...\comparing-periods.xlsx"
            DBMS=EXCEL REPLACE;
     RANGE="Sheet1$";
     GETNAMES=YES;
     MIXED=NO;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

data work.selection2;
set WORK.new;
column5=column5/1000;
period="m";
YEAR=year(column7) ;
QTR=QTR(column7) ;
MTH=MONTH(column7);
lastmm=month(column8);
run;

proc tabulate noseps data=work.selection2;
class column1 column2 column3 column4 column6 column7 period;
var column5 column8;
tables column4=''*
       column1='',
       column3=''*column7=''*column5=''*sum='tmt' *f=8.3
    period=' '*column8='lastperiod'*mean=''*f=date7.
/ box=_page_ rts=25  misstext=" ";
where mth le lastmm; /* for some reason I see 0 observations */
run;

I would like to see:

data     2009/10      ....      2011/2012

data1     486500     ....      521500

etc...

Contributor
Posts: 32

Comparing data during the same period as present year

sorry correction on data step (I recalculated column6)

data work.selection2;
set WORK.new;
column5=column5/1000;
period="m";
YEAR=year(column7) ;
QTR=QTR(column7) ;
MTH=MONTH(column7);
lastmm=month(column8);

format column2 date. fy0 date.;

  fy0 = intnx('year.10',column2,0,'B'); *start date of crop year;
   length cropyear $9;
   cropyear   = catx('/',year(fy0),year(fy0)+1);
   fyQT = intck('QTR',fy0,column2)+1;  
run;

Super User
Posts: 10,516

Comparing data during the same period as present year

My approach would be to ensure the dates are SAS date values and then use a custom format.

Proc format;

value MyYear

'01OCT2010'd - '30SEP2011'd = '2010/2011'

'01OCT2011'd - '30SEP2012'd = '2011/2012'

;

run;

Then in most summary procs assigning the format MYYEAR to the date will give you the grouping AND the desired display text.

Super Contributor
Posts: 1,636

Comparing data during the same period as present year

Hi Coba,

Do you want something like this?

data have;

infile cards dlm=' ';

informat column2 $6. column6 $9. column7 date9.;

input column1 $ column2  column3$ column4$ column5 column6 :column7 :& ;

cards;

data1 Oct-10 product1 action1 15000 2010/2011 01-Oct-10

data2 Oct-10 product1 action1 3750 2010/2011 01-Oct-10

data1 Nov-10 product1 action1 16500 2010/2011 01-Oct-10

data2 Nov-10 product1 action1 4125 2010/2011 01-Oct-10

data1 Dec-10 product1 action1 34000 2010/2011 01-Oct-10

data2 Dec-10 product1 action1 8500 2010/2011 01-Oct-10

data1 Jan-11 product1 action1 35500 2010/2011 01-Oct-10

data2 Jan-11 product1 action1 8875 2010/2011 01-Oct-10

data1 Feb-11 product1 action1 50000 2010/2011 01-Oct-10

data2 Feb-11 product1 action1 12500 2010/2011 01-Oct-10

data1 Mar-11 product1 action1 51500 2010/2011 01-Oct-10

data2 Mar-11 product1 action1 12875 2010/2011 01-Oct-10

data1 Apr-11 product1 action1 9800 2010/2011 01-Oct-10

data2 Apr-11 product1 action1 2450 2010/2011 01-Oct-10

data1 May-11 product1 action1 11300 2010/2011 01-Oct-10

data2 May-11 product1 action1 2825 2010/2011 01-Oct-10

data1 Jun-11 product1 action1 12800 2010/2011 01-Oct-10

data2 Jun-11 product1 action1 3200 2010/2011 01-Oct-10

data1 Jul-11 product1 action1 14300 2010/2011 01-Oct-10

data2 Jul-11 product1 action1 3575 2010/2011 01-Oct-10

data1 Aug-11 product1 action1 58000 2010/2011 01-Oct-10

data2 Aug-11 product1 action1 14500 2010/2011 01-Oct-10

data1 Sep-11 product1 action1 59500 2010/2011 01-Oct-10

data2 Sep-11 product1 action1 14875 2010/2011 01-Oct-10

data1 Oct-11 product1 action1 61000 2011/2012 01-Oct-11

data2 Oct-11 product1 action1 15250 2011/2012 01-Oct-11

data1 Nov-11 product1 action1 62500 2011/2012 01-Oct-11

data2 Nov-11 product1 action1 15625 2011/2012 01-Oct-11

data1 Dec-11 product1 action1 40000 2011/2012 01-Oct-11

data2 Dec-11 product1 action1 10000 2011/2012 01-Oct-11

data1 Jan-12 product1 action1 41500 2011/2012 01-Oct-11

data2 Jan-12 product1 action1 10375 2011/2012 01-Oct-11

data1 Feb-12 product1 action1 43000 2011/2012 01-Oct-11

data2 Feb-12 product1 action1 10750 2011/2012 01-Oct-11

data1 Mar-12 product1 action1 44500 2011/2012 01-Oct-11

data2 Mar-12 product1 action1 11125 2011/2012 01-Oct-11

data1 Apr-12 product1 action1 55000 2011/2012 01-Oct-11

data2 Apr-12 product1 action1 13750 2011/2012 01-Oct-11

data1 May-12 product1 action1 56500 2011/2012 01-Oct-11

data2 May-12 product1 action1 14125 2011/2012 01-Oct-11

data1 Jun-12 product1 action1 58000 2011/2012 01-Oct-11

data2 Jun-12 product1 action1 14500 2011/2012 01-Oct-11

data1 Jul-12 product1 action1 59500 2011/2012 01-Oct-11

data2 Jul-12 product1 action1 14875 2011/2012 01-Oct-11

;

run;

proc sql;

create table temp as

  select column1 as data,column6,sum(column5)/1000 as total

    from have

            group by column1,column6;

quit;

proc transpose data=temp out=want(drop=_name_);

var total;

by data;

id column6;

run;

proc print;run;

                       _2010_      _2011_

Obs    data      2011         2012

1     data1    368.20    521.500

2     data2     92.05    130.375

Linlin

Ask a Question
Discussion stats
  • 5 replies
  • 127 views
  • 0 likes
  • 3 in conversation