Sadly paying work comes first :)

This is something like what you're asking for if I understand you, except you'll want to use your DATE variable instead of var1:

proc sql ; create table want as select id, max(case when interval='S' then var3 end) as s_date, max(case when interval='B' then var1 end) as b_date from have group by id; quit;

Don't need to UPDATE, unless you have a good reason to do so I don't imagine, can do it all in one step...

You could also do this with a data step.

data want; set have; by id; if interval='S' then s_date=var3; if interval='B' then b_date=var1; if last.id then output; keep id s_date b_date; run;

Or something like that depending on your specific logic.

Thanks,

Joe

-Joe

On Thu, Feb 10, 2011 at 4:11 PM, Kevin F. Spratt < Kevin.F.Spratt@dartmouth.edu> wrote:

> At 04:12 PM 2/10/2011, you wrote: > >> Another option, by the way, is SQL: >> proc sql undopolicy=optional; >> update have H >> set var3=(select var3 from have V where interval='S' and H.id=V.id); >> quit; >> > > > You see, now you have provided me an answer than I don't really understand. > > However, I find I have a 2nd variation on the theme below. In this > variation, > the second interval (B), if it exists, has a date. I would like all records > for > that patient to have this date, at which point I can sort with a nodupkey > option > and have a single record per subject with the s_date and b_date values (if > they > both exist). > > perhaps this SQL approach would work more easily that figuring out how to > sort ID and > so the B is first (if it exits), which would allow me to use the code you > sent earlier. > But my SQL skills are essentially nonexistent. > > I'm timing you now, last time it took less than 8 minutes for a response. > > > > > > > > Of course it's safer if you have a separate table to update from, but it's >> not exactly a dangerous operation if you don't have risk of disk errors >> and/or can easily recreate table HAVE if one occurs. >> >> -Joe >> >> >> On Thu, Feb 10, 2011 at 3:00 PM, Joe Matise <snoopy369@gmail.com> wrote: >> >> > One way to go: >> > data have; >> > input id interval $ var1 var2 var3; >> > datalines; >> > >> > 1 S 1 1 1 >> > 1 A 2 3 . >> > 1 B 10 4 . >> > 2 S 3 5 1 >> > 2 A 5 . . >> > 2 B 4 . . >> > 2 D . 3 . >> > 3 S 5 7 0 >> > 3 B 6 3 . >> > 3 C 3 2 . >> > 3 D 7 9 . >> > ;;;; >> > run; >> > >> > data want; >> > set have; >> > retain var3a; >> > if not missing(var3) then var3a=var3; >> > var3=var3a; >> > drop var3a; >> > run; >> > >> > -Joe >> > >> > >> > On Thu, Feb 10, 2011 at 2:53 PM, Kevin F. Spratt < >> > Kevin.F.Spratt@dartmouth.edu> wrote: >> > >> >> Suppose the following data structure: >> >> >> >> id interval var1 var2 var3; >> >> 1 S 1 1 1 >> >> 1 A 2 3 . >> >> 1 B 10 4 . >> >> 2 S 3 5 1 >> >> 2 A 5 . . >> >> 2 B 4 . . >> >> 2 D . 3 . >> >> 3 S 5 7 0 >> >> 3 B 6 3 . >> >> 3 C 3 2 . >> >> 3 D 7 9 . >> >> >> >> desired data structure: >> >> >> >> id interval var1 var2 var3; >> >> 1 S 1 1 1 >> >> 1 A 2 3 1 >> >> 1 B 10 4 1 >> >> 2 S 3 5 1 >> >> 2 A 5 . 1 >> >> 2 B 4 . 1 >> >> 2 D . 3 1 >> >> 3 S 5 7 0 >> >> 3 B 6 3 0 >> >> 3 C 3 2 0 >> >> 3 D 7 9 0 >> >> >> >> Note that var1 and var2 have missingness but the missingness of var3 >> >> has to do with >> >> the fact that a value exists when interval=S but not otherwise. The >> >> missingness >> >> for var1 and var2 is to remain, but for var3 I want all missings >> >> within ID after >> >> interval=S to be equal to the nonmissing value for Var3 when interval >> >> level is S. >> >> >> >> Is there some "retain' syntax that I haven't been able to correctly >> >> specify that will >> >> produce the following. If retain in not the way to go, how do I do >> this? >> >> >> >> Running SAS 9.2 on Windows XP 64-bit if this makes a difference. >> >> >> >> Th >> >> >> >> >> >> ______________________________________________________________________ >> >> >> >> Kevin F. Spratt, Ph.D. >> >> Department of Orthopaedic Surgery >> >> Dartmouth Medical School >> >> One Medical Center Drive >> >> DHMC >> >> Lebanon, NH USA 03756 >> >> (603) 653-6012 (voice) >> >> (603) 653-6013 (fax) >> >> Kevin.F.Spratt@Dartmouth.Edu (e-mail) >> >> >> >> Data is not information; >> >> Information is not knowledge; >> >> Knowledge is not understanding; >> >> Understanding is not wisdom. >> >> >> >> - Cliff Stoll and Gary Schubert >> >> >> >> _______________________________________________________________________ >> >> >> > >> > >> > > > ______________________________________________________________________ > > Kevin F. Spratt, Ph.D. > Department of Orthopaedic Surgery > Dartmouth Medical School > One Medical Center Drive > DHMC > Lebanon, NH USA 03756 > (603) 653-6012 (voice) > (603) 653-6013 (fax) > Kevin.F.Spratt@Dartmouth.Edu (e-mail) > > Data is not information; > Information is not knowledge; > Knowledge is not understanding; > Understanding is not wisdom. > > - Cliff Stoll and Gary Schubert > > _______________________________________________________________________ > >