Help using Base SAS procedures

how to retain combination if doesnt occur in next period?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 190
Accepted Solution

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
Super User
Posts: 7,977

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

Posted in reply to munitech4u

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;

View solution in original post


All Replies
SAS Employee
Posts: 232

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

Posted in reply to munitech4u

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?

Posted in reply to munitech4u

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
Super User
Posts: 7,977

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

Posted in reply to munitech4u

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    Smiley TongueLIST 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: 190

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
Super User
Posts: 7,977

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

Posted in reply to munitech4u

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: 11,343

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

Posted in reply to munitech4u

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?

Posted in reply to munitech4u

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: 190

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

Posted in reply to 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

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 322 views
  • 4 likes
  • 6 in conversation