*************************** * SAS REPORT ON "PROC SQL"* *************************** Presented By: * PRABIN THAPA * YING YAN In this presentation we will discuss about the some features within proc sql. We will be discussing about 1. General features of SQL function namely count, average, insert and data mixing 2. PROC TIME PLOT Though proc timeplot is a independent function in itself but it can be used to plot the data-set generated by the SQL. We have used the data sets from the book- "Getting started with "SQL Procedure" and we have extensively used the SAS-HELP sample programs as well. At the end we express our thanks to Professor J. Deddens for giving us opportunity to make this presentation *************************************************; data internat; input flight $3. +5 date date7. +2 dest $3. +8 boarded; informat date date7.; format date date7.; cards; 219 01MAR94 LON 198 622 01MAR94 FRA 207 132 01MAR94 YYZ 115 271 01MAR94 PAR 138 219 02MAR94 LON 147 622 02MAR94 FRA 176 132 02MAR94 YYZ 106 271 02MAR94 PAR 172 219 03MAR94 LON 197 622 03MAR94 FRA 180 132 03MAR94 YYZ 75 271 03MAR94 PAR 147 219 04MAR94 LON 232 622 04MAR94 FRA 137 132 04MAR94 YYZ 117 271 04MAR94 PAR 146 219 05MAR94 LON 160 622 05MAR94 FRA 185 132 05MAR94 YYZ 157 271 05MAR94 PAR 177 219 06MAR94 LON 163 132 06MAR94 YYZ 150 219 07MAR94 LON 241 622 07MAR94 FRA 210 132 07MAR94 YYZ 164 271 07MAR94 PAR 155 ; proc sql outobs=15; select * from work.internat; ************************************************************; data march; input flight $3. +5 date date7. +3 depart time5. +2 orig $3. +3 dest $3. +7 miles +6 boarded +6 capacity; format date date7. depart time5.; informat date date7. depart time5.; cards; 114 01MAR94 7:10 LGA LAX 2475 172 210 202 01MAR94 10:43 LGA ORD 740 151 210 219 01MAR94 9:31 LGA LON 3442 198 250 622 01MAR94 12:19 LGA FRA 3857 207 250 132 01MAR94 15:35 LGA YYZ 366 115 178 271 01MAR94 13:17 LGA PAR 3635 138 250 302 01MAR94 20:22 LGA WAS 229 105 180 114 02MAR94 7:10 LGA LAX 2475 119 210 202 02MAR94 10:43 LGA ORD 740 120 210 219 02MAR94 9:31 LGA LON 3442 147 250 622 02MAR94 12:19 LGA FRA 3857 176 250 132 02MAR94 15:35 LGA YYZ 366 106 178 302 02MAR94 20:22 LGA WAS 229 78 180 271 02MAR94 13:17 LGA PAR 3635 104 250 114 03MAR94 7:10 LGA LAX 2475 197 210 202 03MAR94 10:43 LGA ORD 740 118 210 219 03MAR94 9:31 LGA LON 3442 197 250 622 03MAR94 12:19 LGA FRA 3857 180 250 132 03MAR94 15:35 LGA YYZ 366 75 178 271 03MAR94 13:17 LGA PAR 3635 147 250 302 03MAR94 20:22 LGA WAS 229 123 180 114 04MAR94 7:10 LGA LAX 2475 178 210 202 04MAR94 10:43 LGA ORD 740 148 210 219 04MAR94 9:31 LGA LON 3442 232 250 622 04MAR94 12:19 LGA FRA 3857 137 250 132 04MAR94 15:35 LGA YYZ 366 117 178 271 04MAR94 13:17 LGA PAR 3635 146 250 302 04MAR94 20:22 LGA WAS 229 115 180 114 05MAR94 7:10 LGA LAX 2475 117 210 202 05MAR94 10:43 LGA ORD 740 104 210 219 05MAR94 9:31 LGA LON 3442 160 250 622 05MAR94 12:19 LGA FRA 3857 185 250 132 05MAR94 15:35 LGA YYZ 366 157 178 271 05MAR94 13:17 LGA PAR 3635 177 250 114 06MAR94 7:10 LGA LAX 2475 128 210 202 06MAR94 10:43 LGA ORD 740 115 210 219 06MAR94 9:31 LGA LON 3442 163 250 132 06MAR94 15:35 LGA YYZ 366 150 178 302 06MAR94 20:22 LGA WAS 229 66 180 114 07MAR94 7:10 LGA LAX 2475 160 210 202 07MAR94 10:43 LGA ORD 740 175 210 219 07MAR94 9:31 LGA LON 3442 241 250 622 07MAR94 12:19 LGA FRA 3857 210 250 132 07MAR94 15:35 LGA YYZ 366 164 178 271 07MAR94 13:17 LGA PAR 3635 155 250 302 07MAR94 20:22 LGA WAS 229 135 180 ; proc sql ; select * from work.march; ********************************************************************; proc sql; title 'The SAS System'; select count(*) label='Number of Departing flight' from work.march; *********************************************************************; proc sql; title 'Number of Flights Each Date'; select date, count(date) from work.march group by date; ***********************************************************************; proc sql; title ; select flight, count(flight) as number label='Flight Count', avg(capacity) as capacity , avg(boarded) as avgboard format=6., avg(boarded/capacity*100) as avg_full format=6. label = 'Average Boared in %' from work.march group by flight ; ***************************************************************************; proc sql; title 'The SAS System'; select min(boarded) label='Fewest Passengers', max(boarded) label='Most Passengers' from march; *************************************************************************** proc sql; title 'Average over 70% Capacity for All Flights Except Flight 622'; select flight, avg(boarded/capacity*100) as avgfull format=4.1 label='Avg Pct' from march where flight ^= '622' group by flight having avgfull>60; ********************************************************************************** ********************************************************************************** proc sql; title 'The SAS System'; create table newintl like work.internat; 1702 proc sql; 1703 title 'The SAS System'; 1704 create table newintl 1705 like work.internat; NOTE: Table WORK.NEWINTL created, with 0 rows and 4 columns. 1706 1707 select* from work.newintl; NOTE: No rows were selected. 1708 ****************************************************************; proc sql; insert into newintl select orig, date, dest, boarded from work.march where flight in ('271','302''622'); select* from newintl; ********************************************************************; proc sql; title; insert into work.newintl set flight='501', date='08MAR94'd, dest='MXC', boarded=150 set flight='501', date='09MAR94'd, dest='MXC', boarded=109; select * from work.newintl; *************************************************************; select * from work.newintl(firstobs=6 obs=8); **************************************************************; proc sql; title 'The SAS System'; create table newintl like work.internat; proc sql; insert into newintl select orig, date, dest, boarded from work.march where flight in ('271','302''622'); select* from newintl; proc sql; title; insert into work.newintl values ('779','08MAR94'd,'SJU',123) values ('779','09MAR94'd,'SJU',144); select * from work.newintl; ************************************************************************************; ************************************************************************************; data AA; input flight $ name $; cards; 145 Khang 145 Mike 150 Miller 150 Peter 155 Evanko 157 Lee ; data BB; input flight $ dest $; cards; 145 Brussels 145 Edmonton 150 Paris 150 Detroit 165 Seattle ; title ' '; proc sql; select * from AA n, BB d where n.flight=d.flight; data CC; set AA BB; by flight; proc print; run; *****************************************************************; OPTIONS NODATE NONUMBER NOCENTER; data paper; input author$1-8 section$9-16 title$17-43 @45 time time5. duration; format time time5.; title1 '*** sqlview ***'; cards; Tom Testing Automated Product Testing 9:00 35 Jerry Testing Involving Users 9:50 30 Nick Testing Plan to test, test to plan 10:30 20 Peter Info SysArtificial Intelligence 9:30 45 Paul Info SysQuery Languages 10:30 40 Lewis Info SysQuery Optimisers 15:30 25 Jonas Users Starting a Local User Group 14:30 35 Jim Users Keeping power users happy 15:15 20 Janet Users Keeping everyone informed 15:45 30 Marti GraphicsMulti-dimensional graphics 16:20 40 Marge GraphicsMake your own point! 15:10 35 Mike GraphicsMaking do without color 15:50 15 Jane GraphicsPrimary colors, use em! 16:15 25 ; proc sql; create view ONE as select author, title, time, duration label='Duration', time+duration*60 as endtime format=time5. from paper; select * from ONE; proc timeplot data=ONE; plot time='<' endtime='>' / overlay ref='12:00't hiloc; class author; run; proc sql; create view ONE_time as select * from ONE order by time; proc print data=ONE_time NOOBS; run; proc timeplot data=ONE_time; plot time='<' endtime='>' / overlay ref1='12:00't ref2='14:00't hiloc; class author; run;