CODEWITHSUNDEEP
sas
HermannHH
HermannHH

Reputation: 1772

An efficient way to Copying values in subsequent records - SAS

I have a dataset that is grouped by category variables in the source data. For example:

Bar  | Foo1
     | Foo2
     | Foo3
Bar2 | Foo4
     | Foo5
     | Foo6

After I import the source data, the above would result in the first Variable (Parent) being populated on Record 1 but not #2 and #3. I would like to copy the parent variable's value into record 2 and 3. Thus my output would look more like this:

Bar  | Foo1
Bar  | Foo2
Bar  | Foo3 
Bar2 | Foo4
Bar2 | Foo5
Bar2 | Foo6

I tried using a "LAG" function, but it doesn't seem to be working. Here is my code:

if PARENT ~= "" then do;
   PARENT = PARENT;
end;
else do;
   PARENT = LAG(PARENT);
end;

Upvotes: 2

Views: 532

Answers (3)

Haikuo Bian
Haikuo Bian

Reputation: 906

Another way of doing it is to add a dummy variable, then trick SAS by using Update statement.

data have;
infile cards dlm='|';
    input (var1 var2) (:$8.);
    retain idx 1;
    cards;
Bar  | Foo1
     | Foo2     
     | Foo3
Bar2 | Foo4
     | Foo5
     | Foo6
     ;

data want;
    update have (obs=0) have;
    by idx;
    output;
    drop idx;
run;

Upvotes: 1

Leo
Leo

Reputation: 2135

The lag function returns the last value that was passed to it when it was called, not the value that was last output.

You could do something like the following:

data want;
    set have;
    length last_parent $256;
    retain last_parent;

    if parent = "" then parent = last_parent;
    else last_parent = parent;

    drop last_parent;
run;

You would need to set the length of last_parent to the same length as parent to make sure nothing gets cut off.

Upvotes: 1

Chris J
Chris J

Reputation: 7769

You can retain the non-missing value and apply it to any missing values, e.g.

data want ;
  set have ;

  length last $10. ;
  retain last '' ;

  if not missing(PARENT) then last = PARENT ;
  else PARENT = last ;

  drop last ;
run ;

Upvotes: 3

Related Questions