Hi @Sofianeuk
I am happy to hear you found my code useful. Month can be extracted with a substring just like Year, see examples. If you would prefer numeric variables for Month and Year, it is also possible, see the second example below.
I am sorry to say that I don't understand what you mean with this: i have no idea how it calculated the sum of all year for each product realy an ace solution, so I can't comment on it without a further explanation.
In the first example below, I have made comments for most lines to explain what happens.
data want;
* The set statement takes input one observation after the other, so all code is;
* executed for every observation;
set have;
* Declare an array with the list of elements in the macro variable &monthlist;
array months &monthlist;
* Drop these variables from output together with Total_Qty_Sold;
* It makes no sense any more, because it horisontal sum;
drop &monthlist Total_Qty_Sold i;
* Loop over the array - dim function returns the number if elements;
* With i as index variable, all month cells in one row in the spreadsheet is processed;
* one after another;
do i = 1 to dim(months);
* We need month as variable VALUE instead of a VARIABLE NAME;
* Months{i} is an array reference. It is the same as writing e.g. _201401 in the code,;
* and returns the VALUE of the variable eg. 3;
* But vname(months{i}) returns the VARIABLE NAME of the i'th element i in the array,;
* eg. _201401, and returns that as a VALUE;
* We want to get rid of the underscore, so we take a substring starting with 2;
* So in the loop, the variable Month takes values 201305, 201306 etc,;
Month = substr(vname(months{i}),2);
* In the loop, months{i} has values 4, ., 3, ., 8 etc;
* We only want to process the month, if the quantity sold is not missing.;
if months{i} ne . then do;
* The variable Year is the four first characters in the Month variable;
Year = substr(Month,1,4);
* The variable Month is recoded to contain only characters 5 and 6;
Month = substr(Month,5,2);
* Quantity_Sold is the sales quantity contained in the month variable;
* corresponding to the value in one cell in the spreadsheet;
Qty_Sold = months{i};
* Output to result data set. Without an output statement, there is an ;
* implicit output at the end of the data step, so we would get one;
* output observation per input input observation with the values set;
* in the last loop iteration;
* We want output from every loop iteration, if there is a sales quantity;
output;
end;
end;
run;
* example with numeric Year and Month;
data want;
set have;
array months &monthlist;
drop &monthlist Total_Qty_Sold i cMonth;
do i = 1 to dim(months);
cMonth = substr(vname(months{i}),2);
if months{i} ne . then do;
Year = input(substr(cMonth,1,4),4.);
Month = input(substr(cMonth,5,2),2.);
Qty_Sold = months{i};
output;
end;
end;
run;
