Hi Sandeep,
Sorry for the delay but I had a lot to do on my projects.
I don't know if you have solved your problem in the meantime but I'll still give it a last try. Please find below a new SAS program which, according to the logic I saw in your Excel, should match with your expectations.
Do not hesitate to comment the DROP statements in the code so that you can have a look at the temporary variables that are created. Also, adding PUT statements before, inside, and after the DO loops may help in visualizing what is happening during the execution of the datasteps.
Kind regards,
Florent
data tmp_Results (drop= tmp_: len_:);
set input;
length List_Row List_Qty List_Price $1000 tmp_Row $100;
retain List_Row List_Qty List_Price;
tmp_row_num = _N_;
tmp_Total_Qty = Qty;
/* To keep exactly the same amount of rows as in the excel example */
if tmp_row_num <= 22 then do;
/* In case of Fresh, add the values to the lists */
if strip(upcase(Flag)) = 'FRESH' then do;
List_Row = strip(List_Row) || strip(put(tmp_row_num, best12.))|| ';';
List_Qty = strip(List_Qty) || strip(put(Qty, best12.))|| ';';
List_Price = strip(List_Price) || strip(put(Price, best12.))|| ';';
end; else
/* In case of Cover, remove values from the lists */
if strip(upcase(Flag)) = 'COVER' then do;
do until (tmp_Total_Qty <= 0);
tmp_Row = strip(scan(List_Row, 1, ';') || ';');
tmp_Qty = input(scan(List_Qty, 1, ';'), best12.);
tmp_Price = strip(scan(List_Price, 1, ';') || ';');
len_Price = lengthn(strip(tmp_Price));
len_Qty = lengthn(strip(scan(List_Qty, 1, ';') || ';'));
if tmp_Qty >= tmp_Total_Qty and tmp_Total_Qty > 0 then do;
tmp_Total_Qty = tmp_Total_Qty - tmp_Qty;
if tmp_Total_Qty >= 0 then do;
List_Row = strip(tranwrd(List_Row, strip(tmp_Row), ''));
List_Qty = strip(substr(List_Qty, len_Qty+1));
List_Price = strip(substr(List_Price, len_Price+1));
end; else
if tmp_Total_Qty < 0 then do;
List_Qty = strip(put(-tmp_Total_Qty, best12.)) || ';' || strip(substr(List_Qty, len_Qty+1));
end;
end; else
if tmp_Total_Qty > tmp_Qty and tmp_Total_Qty > 0 then do;
tmp_Total_Qty = tmp_Total_Qty - tmp_Qty;
List_Row = strip(tranwrd(List_Row, strip(tmp_Row), ''));
List_Qty = strip(substr(List_Qty, len_Qty+1));
List_Price = strip(substr(List_Price, len_Price+1));
end;
end;
end;
output tmp_Results;
end;
run;
data Results (drop= List_: tmp_:);
set tmp_Results;
List_Row = lag1(List_Row);
List_Qty = lag1(List_Qty);
List_Price = lag1(List_Price);
if strip(upcase(Flag)) = 'COVER' then do;
tmp_nb_items = countw(List_Row);
tmp_numerator = 0;
tmp_denominator = 0;
do tmp_i=1 to tmp_nb_items;
tmp_Qty = input(scan(List_Qty, tmp_i, ';'), best12.);
tmp_Price = input(scan(List_Price, tmp_i, ';'), best12.);
tmp_numerator = sum(tmp_numerator, tmp_Qty * tmp_Price);
tmp_denominator = sum(tmp_denominator, tmp_Qty);
end;
avg_price = divide(tmp_numerator, tmp_denominator);
end;
run;
Click image to register for webinar
