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
