BookmarkSubscribeRSS Feed
coba
Calcite | Level 5

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.

5 REPLIES 5
Linlin
Lapis Lazuli | Level 10

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

coba
Calcite | Level 5

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

coba
Calcite | Level 5

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;

ballardw
Super User

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.

Linlin
Lapis Lazuli | Level 10

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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