CODEWITHSUNDEEP
sas
Gifty
Gifty

Reputation: 21

Setting cutoff period SAS

I am having a problem with a dataset that looks like the one below. It is an inventory count of different location/weeks:

data have;
    input itm location $ week inv;
cards;
3 x 1 30
3 x 2 20
3 x 3 0
3 x 4 5
3 y 1 100
3 y 2 90
3 y 3 0
3 y 4 6
4 x 1 30
4 x 2 0
4 x 3 40
4 x 4 10
;
run;

Here is the issue...once the inventory hits 0 for a specific location/item combination, I want all remaining weeks for that combination to be imputed with 0. My desired output looks like this:

data want;
    input itm location $ week inv;
cards;
3 x 1 30
3 x 2 20
3 x 3 0
3 x 4 0
3 y 1 100
3 y 2 90
3 y 3 0
3 y 4 0
4 x 1 30
4 x 2 0
4 x 3 0
4 x 4 0
;
run;

I'm fairly new to SAS and don't know how to do this. Help?!

Thank you!

Upvotes: 2

Views: 180

Answers (2)

D. Josefsson
D. Josefsson

Reputation: 1052

The following will use proc sql to give the wanted result. I have commented inline why I do different steps.

proc sql;
  /* First of all fetch all observations where the inventory is depleated.*/
  create table work.zero_inv as
    select *, min(week) as min_zero_inv_week
    from work.have where inv = 0
    /* If your example data set had included several zero inventory weeks, without the follwing "commented" code you would got duplicates. I'll leave the excercise to explain this to you. Just alter your have data set and see the difference.*/
    /*group by itm, location
    having (calculated min_zero_inv_week) = week*/;

  create table work.want_calculated as
      /* Since we have fetched all weeks with zero inventories, we can use a left join and only update weeks that follows those with zeros and leave the inventory untouched for the rest.*/
    select t1.itm, t1.location, t1.week,
      /* Since we use a left join, we can check if the second data sets includes any rows at all for the specific item at the given location. */
      case when t2.itm is missing or t1.week <= t2.week then t1.inv else t2.inv end as inv
    from work.have as t1
    left join work.zero_inv as t2
      on t1.itm = t2.itm and t1.location = t2.location
    /* proc sql does not promise to keep the order in your dataset, therefore it is good to sort it.*/
    order by t1.itm, t1.location, t1.week;
run;

proc compare base=work.want compare=work.want_calculated;
  title 'Hopefully no differences';
run;

Remember that stackoverflow isn't a "give me the code" forum, it is customary to try some solutions by yourself first. I'll cut you some slack since this is your first question; Welcome to SO :D.

Upvotes: 3

DaBigNikoladze
DaBigNikoladze

Reputation: 661

You can do that in the following steps:

  • by statement to indicate the order (the input dataset must be sorted accordingly)
  • retain statement to pass the value of a control variable (reset) to the following rows
  • deactivate the imputation (reset=0) for every first location/item combination
  • activate the imputation (reset=1) for zero values of inv
  • set to 0 if the imputation is active

Code:

data want (drop=reset);
    set have;
    by itm location week;
    retain reset;
    if first.location then reset=0;
    if (inv = 0) then reset=1;
    else if (reset = 1) then inv=0;
run;

The value of reset remains constant from row to row until explicitly modified.

The presence of the variable week in the by statement is only to check that the input data is chronologically sorted.

Upvotes: 5

Related Questions