SAS SQL Left Join default order

[jwillis]

How do I maintain the original order of table 'a' in a "left join on" in a SQL Statement?  My left join sql statement returns all of the table a values in alphabetic order.  I want to maintain the input data order of table 'a'.

proc sql; drop table work.have; quit;
data work.have;
input name $;
cards;
john
jane
jack
jill
jenn
jeff
john
john
jill
jenn
jack
jack
jane
jack
jack
jane
jenn
jenn
;
run;

   
proc freq data=have order=data noprint;
        tables name / missing nocum nopercent out=havecount;
run;      

/*** maintains original order ***/ 

proc sql;
  drop table work.originalorder;
  CREATE TABLE work.originalorder as
  SELECT a.name,
         (select distinct count from work.havecount as b
              where a.name = b.name) as count
  FROM work.have as a
   ;
quit;

/*** changes the order of the data to alphabetical order ***/

proc sql;
  drop table work.alphaorder;
  CREATE TABLE work.alphaorder as
  SELECT a.name,
         b.count
  FROM work.have as a left join work.havecount as b
     on a.name = b.name
   ;
quit;

options nocenter;

     title " work.originalorder ";
proc print data=work.originalorder (obs=97);
run;
     title " ";

     title " work.alphaorder ";
proc print data=work.alphaorder (obs=97);
run;
     title " ";

work.originalorder    
Obs    name    count
  1    john      3
  2    jane      3
  3    jack      5
  4    jill      2
  5    jenn      4
  6    jeff      1
  7    john      3
  8    john      3
  9    jill      2
10    jenn      4
11    jack      5
12    jack      5
13    jane      3
14    jack      5
15    jack      5
16    jane      3
17    jenn      4
18    jenn      4

work.alphaorder     
Obs    name    COUNT
  1    jack      5
  2    jack      5
  3    jack      5
  4    jack      5
  5    jack      5
  6    jane      3
  7    jane      3
  8    jane      3
  9    jeff      1
10    jenn      4
11    jenn      4
12    jenn      4
13    jenn      4
14    jill      2
15    jill      2
16    john      3
17    john      3
18    john      3

[Quentin]

Hi,

SQL is a logical set language, doesn't care about order much.    If you want to insist that your order be maintained, suggest you add a rownum variable to work.have, then order by rownum.

HTH,

-Q.