topic Appending data to the lead of a record in SAS Programming

Appending data to the lead of a record

Mulvenon — Thu, 11 Jul 2024 17:12:48 GMT

Data problem where I need to append information to records ... my data are presented in the following fashion:

GHIN Name Date Tees F9 B9 Tot CR CS

001362 Smith 06/10/2024 Green 36 38 74 68.1 119

. 06/14/2024 Green 38 41 79 . .

. 06/22/2024 Green 41 40 81 . .

001362 Smith 06/11/2024 Green_White 41 38 79 70.2 116

. 06/20/2024 Green_White 38 38 65 . .

I need the data to be represented as:

GHIN Name Date Tees F9 B9 Tot CR CS

001362 Smith 06/10/2024 Green 36 38 74 68.1 119

001362 Smith 06/14/2024 Green 38 41 79 68.1 119

001362 Smith 06/22/2024 Green 41 40 81 68.1 119

001362 Smith 06/11/2024 Green_White 41 38 79 70.2 116

001362 Smith 06/20/2024 Green_White 38 38 65 70.2 116

Does anyone know a quick way to complete this task? The dataset has approximately 2000 observations so need a programming solution.

Re: Appending data to the lead of a record

PaigeMiller — Thu, 11 Jul 2024 17:17:36 GMT

So when GHIN or NAME is missing, just use the last non-missing? Same for CR and CS?

data want;
    set have;
    retain ghin1 name1 cr1 cs1;
    if not missing(ghin) then ghin1=ghin;
    if not missing(name) then name1=name;
    if not missing(cr) then cr1=cr;
    if not missing(cs) then cs1=cs;
run;

Re: Appending data to the lead of a record

data_null__ — Thu, 11 Jul 2024 17:53:09 GMT

The update trick saves you having to know the variables. Typical LOCF.

data have;
   retain dummy 1;
   input  GHIN $ Name $  Date:mmddyy.  Tees :$16.  F9 B9 Tot CR CS;
   format date mmddyy10.;
   cards;
001362  Smith  06/10/2024  Green       36 38 74 68.1    119
.           .  06/14/2024  Green       38 41 79    .         .
.           .  06/22/2024  Green       41 40 81    .         .
001362  Smith  06/11/2024  Green_White 41 38 79 70.2  116
.           .  06/20/2024  Green_White 38 38 65   .      .  
;;;;
   run;
proc print;
   run;

data want;
   update have(keep=dummy obs=0) have;
   by dummy;
   output; *:-);
   run;

proc print;
   run;

Re: Appending data to the lead of a record

Mulvenon — Thu, 11 Jul 2024 17:54:21 GMT

That worked exactly as I needed.

Thanks Paige.