Date:         Tue, 9 May 2006 21:07:51 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: sql join or datastep merge question
Comments: To: ya.huang@AMYLIN.COM
In-Reply-To:  <200605092040.k49GZDCD027931@mailgw.cc.uga.edu>
Content-Type: text/plain; format=flowed

Ya,

I seem to have forgotten to post the last data step sorry:

data Master ;
infile cards ;
input patient visit value ;
cards ;
1 2 345
1 3 287
2 2 105
;
run ;

data Temp ;
infile cards ;
input Visit ;
cards ;
1
2
3
;
run ;


proc sql ;
create table Need as
  select * from Master
  union
  select * from Temp ;
quit ;


data Need ( Drop = Cnt ;;
set Need ;
by Patient ;

If First.Patient then Cnt = 0 ;

Cnt + 1 ;

If missing( Visit ) then Visit = Cnt ;

run ;


proc print
  data = Need ;
run ;



Toby Dunn





From: Ya Huang <ya.huang@AMYLIN.COM>
Reply-To: Ya Huang <ya.huang@AMYLIN.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: sql join or datastep merge question
Date: Tue, 9 May 2006 16:40:59 -0400

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
 >--