BookmarkSubscribeRSS Feed
jwillis
Quartz | Level 8

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

1 REPLY 1
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 3028 views
  • 0 likes
  • 2 in conversation