Macros for Detecting Negative Values

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Macros for Detecting Negative Values

Hi SAS Community.

 

I have a file of purchases, which hypothetically shows below.

 

Store Week Quantity
1 1 3
1 3 4
1 5 -2
1 7 -2
1 9 5
2 1 3
2 2 7
2 3 8
2 4 -2

 

This is a data for simple sales data where "store" is the store identifier, "Week" is the week identifier and "quantity" is the number of quantity purchased. If you look at the quantity, some of the quantity show negative values (which mean there are product returns). So, we want to eliminate that as it "disturbs" the data (especially when taking the logs) by "aggregating" them.

 

The final data should look:

Store Week Quantity
1 1 3
1 9 5
2 1 3
2 2 7
2 3 6

 

If you notice, there are product return for store 1 in week 5 and 7, so we subtract it from the last inventory (week 4). Similarly, store 2 has returned 2 products at week 4 in which we subtracted from week 3. The other values remain.

 

Any help will be greatly appreciated. Thank you in advance.

 

Best,

David


Accepted Solutions
Solution
‎06-09-2016 03:40 AM
Grand Advisor
Posts: 9,584

Re: Macros for Detecting Negative Values

I love this question.



data have;
infile cards expandtabs truncover;
input Store	Week	Quantity;
cards;
1	1	3
1	3	4
1	5	-2
1	7	-2
1	9	5
1	10	5
1	11	-8
1	12	-1
1   13   4
1   14   4
1   15   2
1   16   -4
1   17   4
1   18   4
2	1	3
2	2	7
2	3	8
2	4	-2
;
run;
data temp;
 set have;
 sign=sign(Quantity);
 if sign=1 then sign=week;
run;
data temp;
 set temp;
 by Store sign notsorted;
 retain new_week;
 if first.sign then new_week=ifn(sign=-1,week,sign);
run;
proc summary data=temp;
by Store sign new_week notsorted;
var Quantity;
output out=x(drop=_:) sum=;
run;
proc sort data=x ;
by Store descending new_week;
run;
data x;
 set x;
 by Store;
 if first.Store or sign=-1 then group+1;
run;
data x;
 set x;
 by group;
 retain is_negative ;
 if first.group then do;
  sum=0;
  is_negative=ifn(sign=-1,1,0);
 end;
 sum+Quantity;
 if sum gt 0;
run;
data want(keep=Store new_week new_Quantity );
 set x;
 by group;
 if not is_negative then new_Quantity=Quantity;
  else do;
   if first.group then new_Quantity=sum;
    else new_Quantity=Quantity;
  end;
run;

proc sort data=want;
by Store new_week;
run;


View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,240

Re: Macros for Detecting Negative Values

Erm, maybe I am missing something, but this is as simple as:

data want;
  set have (where=(quantity > 0));
  by store;
  if first.store or last.store then output;
run;

I assumed that it was the first and last of each store as that is what your output shows.  Note, there is 99.9% never a need to use macros to do processing, Base SAS is a complete language which can do almost anything.

Contributor
Posts: 21

Re: Macros for Detecting Negative Values

Thank you for your reply. Maybe I explain it in a very bad way. Apologised for that. Say if I have this data:

 

Store Week Quantity
1 1 3
1 2 5
1 3 3
1 4 7
1 5 -2

 

I want to "eliminate" the negative by aggregating with the last value. So, my final model will look as:

Store Week Quantity
1 1 3
1 2 5
1 3 3
1 4 5

 

If you see, the "-2" is not deleted but it is incorporated with the last positive observation. These negative numbers can come in the middle or end of the data. It doesn't have to be the last. That's why I think I do need macros here.

 

The syntax that you are proposing here only takes into account the first and last store. It doesn't take into account the aggregation.

 

Best,


David

 

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,240

Re: Macros for Detecting Negative Values

Well, this works, it doesn't cover every eventuality though, what if the first one is a negative, what if there are two in a row etc.  For all your conditions you would need to update this, but it can get you started.  The idea, simply reverse sort the data, and retain the last value, then you conditionally add it or output.

data have;
  input Store	Week	Quantity;
datalines;
1	1	3
1	2	5
1	3	3
1	4	7
1	5	-2
;
run;
proc sort data=have;
  by store descending week;
run;
data want;
  set have;
  retain remove;
  if quantity < 0 then remove=quantity;
  else do;
    quantity=quantity + remove;
    output;
  end;
run;
proc sort data=want (drop=remove);
  by store week;
run;

You could of course join the dataset to itself, I find the sort is simpler.

Esteemed Advisor
Posts: 5,198

Re: Macros for Detecting Negative Values

No need for macro programming in this case. And even, it's not suited.

Resort your data set by descending week.

Then you retain the Quantity variable, so that you could subtract/delete any values/rows per your requirement.

Data never sleeps
Contributor
Posts: 21

Re: Macros for Detecting Negative Values

Hi Linus,

 

I do agree but I have 25,000 stores which will be quite tedious for me to do that. So, I do need macros (especially in the aggregation). Let me know if you are confused and can help me further Smiley Happy

 

Happy to hear from you.

 

Best,

David

Esteemed Advisor
Posts: 5,198

Re: Macros for Detecting Negative Values

Well, I'm confused... :-)

But as long as your stores are in the the same data set/file whatever, no macros!

Just use

BY Store;

And som firs. and last. logic and retain.

Data never sleeps
Contributor
Posts: 21

Re: Macros for Detecting Negative Values

Thank you for your reply. Maybe I explain it in a very bad way. Apologised for that. Say if I have this data:

 

Store Week Quantity
1 1 3
1 2 5
1 3 3
1 4 7
1 5 -2

 

I want to "eliminate" the negative by aggregating with the last value. So, my final model will look as:

Store Week Quantity
1 1 3
1 2 5
1 3 3
1 4 5

 

If you see, the "-2" is not deleted but it is incorporated with the last positive observation. These negative numbers can come in the middle or end of the data. It doesn't have to be the last.

 

 

Best,


David

Esteemed Advisor
Posts: 6,693

Re: Macros for Detecting Negative Values

I would not do that. By aggregating into a different week you partially lose information (point in time). Either get rid of the weeks at all by aggregating for stores (eliminating all point-in-time information), or keep all weeks.

Reasoning:

week quantity
1       4
2       6
3      -3
4       2

If the -3 in week 3 are merchandise sold in week 1, after your transformation you would give a wrong answer to the question "when was the best week?"

If you want to eliminate the negatives at all, do that by assigning them to the correct original purchases.

You will also have all kinds of troubles in fringe cases (ie your observation period starts with a negative)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,995

Re: Macros for Detecting Negative Values

In general, the advice you have received so far is on the right track.  Sort by STORE DESCENDING WEEK, and then calculate.

 

Here are a couple of issues that you might want to think about.

 

What should happen if QUANTITY=0 on an incoming observation?  Should that observation remain in the data or should it be deleted?  The program might have to distinguish between an original 0 vs. a 0 due to the application of returns.

 

Is it possible that the returns will sum up to more than the previous quantity?  The returns might remove one week of data, and in addition to that partially reduce another week of data.

Solution
‎06-09-2016 03:40 AM
Grand Advisor
Posts: 9,584

Re: Macros for Detecting Negative Values

I love this question.



data have;
infile cards expandtabs truncover;
input Store	Week	Quantity;
cards;
1	1	3
1	3	4
1	5	-2
1	7	-2
1	9	5
1	10	5
1	11	-8
1	12	-1
1   13   4
1   14   4
1   15   2
1   16   -4
1   17   4
1   18   4
2	1	3
2	2	7
2	3	8
2	4	-2
;
run;
data temp;
 set have;
 sign=sign(Quantity);
 if sign=1 then sign=week;
run;
data temp;
 set temp;
 by Store sign notsorted;
 retain new_week;
 if first.sign then new_week=ifn(sign=-1,week,sign);
run;
proc summary data=temp;
by Store sign new_week notsorted;
var Quantity;
output out=x(drop=_:) sum=;
run;
proc sort data=x ;
by Store descending new_week;
run;
data x;
 set x;
 by Store;
 if first.Store or sign=-1 then group+1;
run;
data x;
 set x;
 by group;
 retain is_negative ;
 if first.group then do;
  sum=0;
  is_negative=ifn(sign=-1,1,0);
 end;
 sum+Quantity;
 if sum gt 0;
run;
data want(keep=Store new_week new_Quantity );
 set x;
 by group;
 if not is_negative then new_Quantity=Quantity;
  else do;
   if first.group then new_Quantity=sum;
    else new_Quantity=Quantity;
  end;
run;

proc sort data=want;
by Store new_week;
run;


Contributor
Posts: 21

Re: Macros for Detecting Negative Values

Thanks KSharp! Excellent Smiley Happy This is what I want


Grand Advisor
Posts: 9,584

Re: Macros for Detecting Negative Values

Sorry. My last code is not right. If you have such scenario :

Store	Week	Quantity;
1	1	3
1	3	4
1	5	-2
1	7	-2
1	9	5
1	10	5
1	11	-10
1	12	-1

--->
Store	Week	Quantity;
1	1	2



Try this Hash Table, It ought to be right:



data have;
infile cards expandtabs truncover;
input Store	Week	Quantity;
cards;
1	1	3
1	3	4
1	5	-2
1	7	-2
1	9	5
1	10	5
1	11	-10
1	12	-1
1   13   4
1   14   4
1   15   2
1   16   -4
1   17   4
1   18   4
2	1	3
2	2	7
2	3	8
2	4	-2
;
run;
data want;
 if _n_=1 then do;
  if 0 then set have(rename=(Store=_Store Week=_Week Quantity=_Quantity));
  declare hash h(ordered:'y');
  declare hiter hi('h');
  h.definekey('k');
  h.definedata('_Store','_Week','_Quantity');
  h.definedone();
 end;
set have;
by Store;
if first.Store then k=0;
k+1;
if Quantity gt 0 then h.add(key:k,data:Store,data:Week,data:Quantity);
 else do;
 
  dif=Quantity;
  do i=k-1 to 1 by -1; 
   if h.find(key:i)=0 then do;
    dif=sum(dif,_Quantity);
    if dif gt 0 then do;
     h.replace(key:i,data:_Store,data:_Week,data:dif);
     leave;
    end;
    else h.remove(key:i);   
   end;   
  end;
  
 end;
 
if last.Store then do;
 do while(hi.next()=0);
  output;
 end;
 h.clear();
end;
keep _:;
run;



☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 768 views
  • 0 likes
  • 6 in conversation