topic Re: how to retain combination if doesnt occur in next period? in SAS Procedures

how to retain combination if doesnt occur in next period?

munitech4u — Thu, 25 Sep 2014 10:34:11 GMT

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

Re: how to retain combination if doesnt occur in next period?

Community_Help — Thu, 25 Sep 2014 11:02:11 GMT

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

Re: how to retain combination if doesnt occur in next period?

mohamed_zaki — Thu, 25 Sep 2014 11:10:02 GMT

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

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

Re: how to retain combination if doesnt occur in next period?

RW9 — Thu, 25 Sep 2014 12:21:46 GMT

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;

Re: how to retain combination if doesnt occur in next period?

munitech4u — Thu, 25 Sep 2014 12:40:19 GMT

@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.

Re: how to retain combination if doesnt occur in next period?

RW9 — Thu, 25 Sep 2014 12:56:50 GMT

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

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;

Re: how to retain combination if doesnt occur in next period?

Malv72 — Thu, 25 Sep 2014 16:49:35 GMT

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;

Re: how to retain combination if doesnt occur in next period?

ballardw — Thu, 25 Sep 2014 17:58:02 GMT

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.

Re: how to retain combination if doesnt occur in next period?

munitech4u — Mon, 13 Oct 2014 19:42:29 GMT

.... 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