topic Re: Filling blank cells with data till the next filled cell using do loops in SAS Programming

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

Ricardo96 — Mon, 10 May 2021 12:36:33 GMT

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.

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

novinosrin — Mon, 10 May 2021 13:04:23 GMT

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;

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

mkeintz — Mon, 10 May 2021 14:18:06 GMT

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.

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

twildone — Mon, 10 May 2021 14:53:50 GMT

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

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

mkeintz — Mon, 10 May 2021 18:16:29 GMT

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;

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

Ricardo96 — Fri, 14 May 2021 09:34:08 GMT

@novinosrin Thank you for your assistance much appreciated

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

Ricardo96 — Fri, 14 May 2021 09:40:42 GMT

@mkeintz Thank you