BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
munitech4u
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
Community_Help
SAS Employee

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

mohamed_zaki
Barite | Level 11

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Calcite | Level 5

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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