loopssasduplicatescopy-paste

Copying data from one row to other rows with the same key value in SAS


I am running into a problem where I would like to duplicate data from one row that contains data to other rows with the same key value, but does not contain any additional data. However, ONLY conditioned if variable 3 or V3 is equal to "Cardio". For instance. I have a dataset that looks like this.

enter image description here

The first two row contain the same key, but the second row does not contain any other information other than the key (no value for v1,v2, v3 etc.) Because the first row has "cardio" for V3, this row would need to be duplicated to the second row that does not contain the information. Now the second image is what I want the dataset to look like.

enter image description here

How am I able to do that in SAS? Thanks in advance.


Solution

  • Mainly you are asking for a simple last observation carried forward. Which you can implement easily using the UPDATE statement. Use an empty version of the original dataset as the source data and the full original dataset as the transactions to be applied. Add an OUTPUT statement so every observation is written instead of just the final observation per BY group.

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

    If you really want to treat the zeros as missing you will have add a step to convert them to such first.

    data for_update ;
      set have;
      if v1=0 then v1=.;
    run;
    data want;
      update for_update (obs=0) for_update ;
      by key;
      output;
    run;