Date:         Mon, 18 Aug 2008 13:24:22 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: how to copy a row
Comments: To: "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

Here is a fix for visits in numeric order, but not + 1 from the previous
visit:

This will work for visits not in numeric exact sequence if one assigns
another variable based on the numeric ordering of the visit, like this where
I'm adding a variable "visitseq", but again it doesn't carry forward a value
more than one visit (i.e. a weight of a patient isn't repeated up for every
visit from the time it is taken until it is missing).

data test;
infile cards missover;
input patid visit adm dose wt;
obsnum + 1;
cards;
001 1 . 32 78
001 2 25 54 78
001 3 . . .
002 1 56 54 90
002 2 60 56 .
003 1 . 55 88
;
proc sort data=test;
by patid visit;
run;

data test2;
set test;
by patid;
if first.patid then visitseq=1;
if not first.patid then visitseq + 1;
run;

proc sql;
create TABLE newtest as
select t1.patid, t1.visit,
coalesce(t1.adm, t2.adm) as adm,
coalesce(t1.dose, t2.dose) as dose,
coalesce(t1.wt, t2.wt) as wt
from test2 t1
left join
test2 t2
on t1.patid = t2.patid and t1.visitseq = (t2.visitseq+1);
quit;


----- Original Message -----
From: Choate, Paul@DDS
To: SAS-L@LISTSERV.UGA.EDU
Sent: Monday, August 18, 2008 12:02 PM
Subject: Re: how to copy a row


Hi Pausha -

This sort of thing can be done with a reflexive SQL left join and
coalesce statements also.

data test;
  infile cards missover;
  input patid visit adm dose wt;
cards;
001 1 52 32 78
001 2 25 54 78
001 3 . . .
002 1 56 54 90
002 2 60 . .
003 1 . 55 88
;

proc sql;
  create TABLE newtest as
    select t1.patid, t1.visit,
           coalesce(t1.adm, t2.adm) as adm,
           coalesce(t1.dose, t2.dose) as dose,
           coalesce(t1.wt, t2.wt) as wt
    from test t1
           left join
         test t2
    on   t1.patid = t2.patid and t1.visit = (t2.visit+1);
quit;


Depending on what you need this may have one advantage - this only
compares to one week prior, if a week is missing the data isn't merged,
so if a patient has week 1 and 3 records, but no week 2 record, the week
1 record will not be moved forward to week 3.

Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
pausha
Sent: Monday, August 18, 2008 8:50 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: how to copy a row

i have this strangle condition and need some help
I have a dataset with patients who visit every week ,say if they miss
a week i still have to have their data copied form previous week for
that missed one.
eg

pat-id   visit   adm    dose   wt

001        1    52        32      78
001        2    25        54      78
001        3     .           .         .
002       1      56      54       90
002       2       .         .         .

in this case i would have to have the previous row copied to the
visits that is missing
help with logic to populate the whole row

i have 40 variables in a row.
thanks