SAS SQL Left Join default order

jwillis — Mon, 28 Apr 2014 17:44:46 GMT

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

Re: SAS SQL Left Join default order

Quentin — Mon, 28 Apr 2014 18:03:12 GMT

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.

topic Re: SAS SQL Left Join default order in SAS Procedures

SAS SQL Left Join default order

Re: SAS SQL Left Join default order