topic Re: Comparing data during the same period as present year in SAS Procedures

Comparing data during the same period as present year

coba — Wed, 25 Jan 2012 17:01:18 GMT

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/2011	2011/2012
data1	250700	521500
data2	62675	130375

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.

Re: Comparing data during the same period as present year

Linlin — Wed, 25 Jan 2012 22:03:03 GMT

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

Comparing data during the same period as present year

coba — Thu, 26 Jan 2012 10:14:46 GMT

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...

Comparing data during the same period as present year

coba — Thu, 26 Jan 2012 10:22:44 GMT

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;

Comparing data during the same period as present year

ballardw — Thu, 26 Jan 2012 18:26:11 GMT

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.

Comparing data during the same period as present year

Linlin — Fri, 27 Jan 2012 00:04:22 GMT

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