Filling blank cells with data till the next filled cell using do loops

[Ricardo96]

How do i write a do while loop to fill the cells with PnP in the Retailer column up until it finds PEP and fill the blank lines after PEP with PEP. 

How do i accommodate if there is a larger dataset with a lot of Retailer names that follow this same format.

when i write is as:

data R_and_P;

set work.R_and_P;

Do Until(Retailer = "PEP);

if Retailer = " " then retailer = "PnP";

output;

run;

The code goes into an infinite Loop and crashes my machine.

Thank you for your assistance in advance.

[novinosrin · Accepted solution]

Hi @Ricardo96 You do not need a loop or any fancy logic. Just retain the non missings across each datastep iteration capturing in a temporary variable and plug in the missings from the non missing temp variable. Simple as that-

data want;
 set your_dataset;
 retain _r;
 length _r $10;/*set this to the length of retailer variable length*/
 if not missing(retailer) then _r=retailer;
 else retailer=_r;
 drop _r;
run;

[Ricardo96]

@novinosrin  Thank you for your assistance much appreciated

[mkeintz]

Another way to do this is to use the implied retain of all variables accessed via a SET statement.  In the program below the IF ... then SET  ... POINT= statement is the only one that reads in (unrenamed) RETAILER, so that value is retained until then next non-blank _R (renamed from RETAILER): 

data have;
  input retailer $3.  product :$10.;
datalines;
PNP Coffee
    Biscuits
    Tea
    Milk
    Sugar
PEP Pants
    Shirts
    Hoodies
    Beanies
    Socks
run;
data want (drop=_:);
  set have (rename=(retailer=_r));
  if _r ^=' ' then set have point=_n_;
run;

This can be especially useful if you have lots of variable to retain, not just one, because you won't have to make a lot of assignment statements to retrieve the retained values.  

This program, however, won't preserve the order of the variables (retailer will be on the far right).  But if you want to preserve the original order of variables, just stick in an "if 0 then set have;" statement prior to the first set have.

[twildone]

Hi Mkeintz….you have indicated that your approach can be useful if you have lots of variables to retain. What would need to be added or modified so that other variables with missing entries would be filled in....Thanks

[mkeintz]

If you had the same data structure, but with dozens of variables all non-missing only in the first record for each non-blank retail value, then you could use the program almost unchanged:

data have;
  infile datalines truncover ;
  input retailer $3.  product :$10.  source :$4.;
datalines;
PNP Coffee     AAA
    Biscuits
    Tea
    Milk
    Sugar
PEP Pants      BBB
    Shirts
    Hoodies
    Beanies
    Socks
run;
data want (drop=_:);
  if 0 then set have;
  set have (rename=(retailer=_r) drop=source);
  if _r ^=' ' then set have point=_n_;
run;

[Ricardo96]

@mkeintz Thank you