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.
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
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...
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;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.