The output was from testing of Toby's code, mine should looks likes this:

patient visit value

1 1 . 1 2 345 1 3 287 2 1 . 2 2 105 2 3 .

Where visit is not missing.

-----Original Message----- From: Ya Huang [mailto:ya.huang@AMYLIN.COM] Sent: Tuesday, May 09, 2006 1:41 PM To: SAS-L@LISTSERV.UGA.EDU; Jake Booter Cc: Huang, Ya Subject: Re: sql join or datastep merge question

You have received two SQL solutions, of which Toby's solution is quite interesting, unfortunately you'll get missing value for the visit. I have here a data step solutions, need two step though:

data xxx; input patient visit value; cards; 1 2 345 1 3 287 2 2 105 ;

data yyy; input visit; cards; 1 2 3 ;

data zzz; if 0 then set yyy nobs=nobs; set xxx; by patient; if first.patient then do; do i=1 to nobs; set yyy point=i; output; end; end; drop value; run;

data xxx; merge xxx zzz; by patient visit; run;

proc print; run;

patient visit value ---------------------------- 1 . . 1 2 345 1 3 287 2 . . 2 2 105 3 . .

HTH

Ya

On Tue, 9 May 2006 16:05:09 -0400, jakeboot@gmail.com <jakeboot@GMAIL.COM> wrote:

>I have data as follows: >patient visit value >--------- ------ ------- >1 2 345 >1 3 287 >2 2 105 > >but I have a template of expected visits as: >visit >----- >1 >2 >3 > >Is there a way via merge or sql where I can use the template to obtain the >following? >patient visit value >--------- ------- -------- >1 1 . >1 2 345 >1 3 287 >2 1 . >2 2 105 >2 3 . > >Thanks, for any information or techniques on this. > >Jake >--