how to retain combination if doesnt occur in next period?

[munitech4u]

I have situation like this:

For each week there is a combination of key,product and some value for it.

  I want to retain the value in next week, if that particular combination of key and product doesnt occur

[Community_Help]

HI there .... I noticed your question and moved it to the SAS Procedures Community where you might get an answer more quickly

[mohamed_zaki]

Could you provide sample data, or describe your question more.

What do you mean by "I want to retain the value in next week"?

[RW9]

A bit of a guess this one, create a template of what rows should be in the output and merge this back to original, then retain the last values:

data have;
  week=1; key=1; product="AA"; val=234; output;
  week=1; key=2; product="BB"; val=45; output;
  week=2; key=1; product="AA"; val=98; output;
  week=3; key=1; product="AA"; val=23; output;
  week=3; key=2; product="BB"; val=12; output;
  week=4; key=2; product="BB"; val=5; output;
run;

proc sql noprint;
  create table LOOP as
  select  min(WEEK) as MIN_WEEK,
          max(WEEK) as MAX_WEEK,
          min(KEY) as MIN_KEY,
          max(KEY) as MAX_KEY
  from    WORK.HAVE;
  select  distinct PRODUCT
  into    :PLIST separated by '","'
  from    WORK.HAVE;
quit;

data template (keep=week key product);
  set loop;
  do w=min_week to max_week;
    do k=min_key to max_key;
      do p="&plist.";
        week=w;
        key=k;
        product=p;
        output;
      end;
    end;
  end;
run;

proc sql;
  create table WANT as
  select  COALESCE(A.WEEK,B.WEEK) as WEEK,
          COALESCE(A.KEY,B.KEY) as KEY,
          COALESCE(A.PRODUCT,B.PRODUCT) as PRODUCT,
          B.VAL
  from    WORK.TEMPLATE A
  left join WORK.HAVE B
  on      A.WEEK=B.WEEK
  and     A.KEY=B.KEY
  and     A.PRODUCT=B.PRODUCT
  order by PRODUCT,
           WEEK,
           KEY;
quit;

data want (drop=val);
  set want;
  by product;
  retain all_val;
  if first.product or val ne . then all_val=val;
run;

[munitech4u]

@rw9, you are going in right direction. Actually I was able to produce the want dataset using some different method. But here is the problem, while you are retaining only last value if its missing, for a particular combination i want to look back in previous weeks for last known value of that combination and replace the missing value with that.

[RW9 · Accepted solution]

Maybe then, transpose up, run through an array loop then transpose back down again:

data have;
  week=1; key=1; product="AA"; val=234; output;
  week=1; key=2; product="BB"; val=45; output;
  week=2; key=1; product="AA"; val=98; output;
  week=3; key=1; product="AA"; val=23; output;
  week=3; key=2; product="BB"; val=12; output;
  week=4; key=2; product="BB"; val=5; output;
run;

proc sort data=have;
  by product key;
run;

proc transpose data=have out=t_have prefix=col;
  by product key;
  var val;
  id week;
run;

proc sql noprint;
  select  max(week)
  into    :MWEEK
  from    WORK.HAVE;
quit;
data t_have2 (drop=i j);
  set t_have;
  array col{&mweek.};
  do i=2 to &mweek.;
    if col{i}=. then do;
      do j=i-1 to 1 by -1;
        if col{i}=. then do;
          if col{j} ne . then col{i}=col{j};       
        end;
      end;
    end;
  end;
run;

proc transpose data=t_have2 out=want;
  by product key;
run;

data want (drop=_name_);
  set want;
  week=input(strip(tranwrd(_name_,"col","")),best.);
run;

[ballardw]

Define "particular combination". Product and key, Product and week, Product week and key? Since you haven't provided any example data it is a bit hard to guess what you may mean here.

[Malv72]

You could also try the retain statement and/or a Cartesian join

/* Thanks RW9 for the data step */

data have;

  week=1; key=1; product="AA"; val=234; output;

  week=1; key=2; product="BB"; val=45; output;

  week=2; key=1; product="AA"; val=98; output;

  week=3; key=1; product="AA"; val=23; output;

  week=3; key=2; product="BB"; val=12; output;

  week=4; key=2; product="BB"; val=5; output;

run;

/* Find the individual weeks */

proc sql;

create table dates as

select distinct week

from have;

quit;

/* Find the individual keys */

proc sql;

create table keys as

select distinct key

from have;

quit;

/* Find the individual products */

proc sql;

create table products as

select distinct product

from have;

quit;

/* Cartesian join to generate a template */

proc sql;

create table date_template as

select a.week

  , b.key

  , c.product

from dates as a

  , keys as b

  , products as c

;

quit;

proc sort data=date_template;

by key product week;

run;

proc sort data=have;

by key product week;

run;

data want;

merge date_template

  have;

by key product week;

/* Retain the last value assigned */

retain val1;

/* Reset the value is the product is different */

if product ne lag1(product) then val1 = 0;

/* If val not missing assign val to val1 */

if val ne . then val1 = val;

run;

[munitech4u]

.... I have the situation modified a little bit. I want to retain the key product combination from only if a a key is missing in particular week