How to calculate the differences in sales at different reference dates

[Ccasagran737]

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

[mkeintz]

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;

[Ccasagran737]

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

[Ksharp · Accepted solution]

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;