## how to retain combination if doesnt occur in next period?

Solved
Regular Contributor
Posts: 196

# how to retain combination if doesnt occur in next period?

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

Accepted Solutions
Solution
‎09-25-2014 08:56 AM
Super User
Posts: 9,599

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

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;

All Replies
SAS Employee
Posts: 232

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

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

Super Contributor
Posts: 490

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

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

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

Super User
Posts: 9,599

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

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    LIST 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;

Regular Contributor
Posts: 196

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

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

Solution
‎09-25-2014 08:56 AM
Super User
Posts: 9,599

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

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;

Super User
Posts: 13,583

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

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.

Occasional Contributor
Posts: 10

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

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;

Regular Contributor
Posts: 196