topic Re: How to calculate the differences in sales at different reference dates in SAS Programming

How to calculate the differences in sales at different reference dates

Ccasagran737 — Fri, 28 May 2021 16:12:14 GMT

Hi, I'm using SAS Studio and I need your help.
I have a list of variables indicating the stock of sales at the end of the month (in the example stock_202012 stock_202101 stock_202102 ...).
I am trying to calculate for each observation the difference between the reference stock (indicated in the example by the variable "date") and the previous month.

This is my input dataset

data have;
	infile datalines;
	input id date ddmmyy8. stock_202012 stock_202101 stock_202102 stock_202103 stock_202104;
	format date date7.;
	datalines;
1 10012021 20 23 22 27 30
2 07042021 10 10 10 10 11
3 23022021 44 44 48 48 50
4 12042021 21 24 27 30 31
5 09022021 30 40 50 43 44
;
run;

This is the output desired

data want;
	infile datalines;
	input id delta_stock;
	datalines;
1 3
2 1
3 4
4 1
5 10
;
run;

For example, for the first observation the delta_stock is obtained by making 23 (stock_202101 since the variable "date" is equal to 10/01/2021) - 20 (stock_202012) equal to 3.

Thanks for your help.

Daniele (Italy).

Re: How to calculate the differences in sales at different reference dates

mkeintz — Fri, 28 May 2021 20:13:22 GMT

Use a two-dimensional array indexed on year (2020:2021) and month (1:12) based on the named variables. Assign a dummy variable to unavailable array elements (i.e. non-existent months for 2020 and 2021)

data have;
	infile datalines;
	input id date ddmmyy8. stock_202012 stock_202101 stock_202102 stock_202103 stock_202104;
	format date date9.;
	datalines;
1 10012021 20 23 22 27 30
2 07042021 10 10 10 10 11
3 23022021 44 44 48 48 50
4 12042021 21 24 27 30 31
5 09022021 30 40 50 43 44
run;

data want (drop=_:);
  set have;
  array stks {2020:2021,1:12} 
    _dummy       _dummy       _dummy       _dummy       _dummy _dummy _dummy _dummy _dummy _dummy _dummy stock_202012
    stock_202101 stock_202102 stock_202103 stock_202104 _dummy _dummy _dummy _dummy _dummy _dummy _dummy _dummy;

  _priordate=intnx('month',date,-1);
  dif=stks{year(date),month(date)} - stks{year(_priordate),month(_priordate)};
run;

Re: How to calculate the differences in sales at different reference dates

Ksharp — Sat, 29 May 2021 12:05:05 GMT

data have;
	infile datalines;
	input id date ddmmyy8. stock_202012 stock_202101 stock_202102 stock_202103 stock_202104;
	format date date7.;
	datalines;
1 10012021 20 23 22 27 30
2 07042021 10 10 10 10 11
3 23022021 44 44 48 48 50
4 12042021 21 24 27 30 31
5 09022021 30 40 50 43 44
;
run;

data want;
 set have;
 delta_stock=
 vvaluex(cats('stock_',put(date,yymmn6.))) -
 vvaluex(cats('stock_',put(intnx('month',date,-1),yymmn6.)));
run;

Re: How to calculate the differences in sales at different reference dates

Ccasagran737 — Mon, 31 May 2021 07:47:52 GMT

thanks for your solution, it works and at the same time I understand better how arrays works