Date:         Wed, 11 Mar 2009 15:49:51 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Combining data in single table: simple question
Comments: To: david5705@HOTMAIL.COM
Content-Type: text/plain; charset="iso-8859-1"

David,

Very nice of you to provide runable data sets :-).   Here's an approach in case you have missing data in one of the quartiles;
I set a data set with just the quartiles first and then left outer join to it- note in the code below I deleted a quartile from the 
first data set, and properly gives me a missing for that.  This would result in a record for the quartile even in the case where the
quartile was missing on all data sets- sometimes useful for catching data that should be there, but isn't.

data results_2008q1 ;
  infile datalines ;
  input quartile result_2008q1 ;
  datalines ;
1  100
3  90
4  200
;
run ;

data results_2008q2 ;
  infile datalines ;
  input quartile result_2008q2 ;
  datalines ;
1  125
2  100
3  95
4  205
;
run ;

data results_2008q3 ;
  infile datalines ;
  input quartile result_2008q3 ;
  datalines ;
1  150
2  105
3  175
4  195
;
run ;


data quartiles;
infile datalines;
input quartile;
datalines;
1
2
3
4
;
run;

proc sql noprint;
create table results_all as
select
quartiles.quartile,
results_2008q1.result_2008q1,
results_2008q2.result_2008q2,
results_2008q3.result_2008q3
from quartiles
left outer join
results_2008q1
on quartiles.quartile=results_2008q1.quartile
left outer join
results_2008q2
on quartiles.quartile=results_2008q2.quartile
left outer join
results_2008q3
on quartiles.quartile=results_2008q3.quartile
order by quartiles.quartile;
quit;



-Mary
  ----- Original Message ----- 
  From: david5705@HOTMAIL.COM 
  To: SAS-L@LISTSERV.UGA.EDU 
  Sent: Wednesday, March 11, 2009 3:09 PM
  Subject: Combining data in single table: simple question


  Dear All:

  Simple question on combining data. How do I create the desired output?
  Simple case presented, but will likely be combining data from multiple
  tables and not just 3.

  Thanks in advance for assistance.

  data results_2008q1 ;
    infile datalines ;
    input quartile result_2008q1 ;
    datalines ;
  1  100
  2  110
  3  90
  4  200
  ;
  run ;

  data results_2008q2 ;
    infile datalines ;
    input quartile result_2008q2 ;
    datalines ;
  1  125
  2  100
  3  95
  4  205
  ;
  run ;

  data results_2008q3 ;
    infile datalines ;
    input quartile result_2008q3 ;
    datalines ;
  1  150
  2  105
  3  175
  4  195
  ;
  run ;

  data results_all ;
    set  results_2008q1
          results_2008q2
          results_2008q3 ;
  run ;

  /* Output obtained:
  quartile  result_2008q1  result_2008q2  result_2008q3
  1          100                  .                     .
  2          110                  .                     .
  3          90                    .                     .
  4          200                  .                     .
  1           .                     125                 .
  2           .                     100                 .
  3           .                     95                   .
  4           .                     205                 .
  1           .                     .                    150
  2           .                     .                    105
  3           .                     .                    175
  4           .                     .                    195

  Desired output:
  quartile  result_2008q1  result_2008q2   result_2008q3
  1          100                 125                  150
  2          110                 100                  105
  3          90                   95                    175
  4          200                 205                  195
  */