Isn't LE the accepted mnemonic for "less than or equal to" rather than "LT="? ... View more

There's also a not exists. select t1.* from main t1 where not exists (select * from minor where key=t1.key); quit; ... View more

proc sql; select t1.* from main t1 left outer join minor t2 on t1.key=t2.key where t2.key is null; quit; ... View more

What happens when proc sql has three variables all called the same thing? You have main.key, minor.key and the newly calculated key. ... View more

I, for one, would have immediately jumped on the presence of the double dash comment symbol as the culprit..........ok...maybe not. ... View more

ummm....so the example we all were working on wasn't what you were actually submitting? ... View more

Well...I'm stumped. ... View more

I'm not sure standard sql syntax allows you to use the calculated column name in the grouping syntax. I'm pretty sure Oracle doesn't. I'm not sure if the odbc driver is doing any parsing or not. You could replace the iDate calculated name with the formula. If this doesn't work, I think you have other issues that are being masked somehow. proc SQL; Connect to odbc(dsn ='database' uid =guest pwd =guest ); Select * from connection to odbc (Select date(date_time)as iDate ,equipmentsernum ,AVG(xxx) ,AVG(yyy) From "tablename" Where date_time >= '2007-01-01' Group By equipmentsernum, date(date_time) ); Disconnect from odbc; ... View more

I think its easier to create the macro variable such that it includes the TO_DATE() as well. Then you can use use the variable plainly as data work.dbdate; format dbvalue1 date9. dbvalue2 date9.; input dbvalue1:date9. dbvalue2:date9.; cards; 30nov2010 31dec2010 run; data _null_; set work.dbdate; call symput('DAY1',"TO_DATE('"||PUT(DBVALUE1,YYMMDDN8.)||"','YYYYMMDD')"); call symput('DAY2',"TO_DATE('"||PUT(DBVALUE2,YYMMDDN8.)||"','YYYYMMDD')"); RUN; proc sql; connect to oracle as db(xxxxxxxxxxxxx); create table tst as select * from connection to db ( select * from mdm.dbdates where date_value between &day1 and &day2 ); disconnect from db; quit; The oracle prepared statement is: select * from mdm.dbdates where date_value between TO_DATE('20101130','YYYYMMDD') and TO_DATE('20101231','YYYYMMDD') ... View more

I think the other commenters are correct..but I believe you would experience significant performance gains if you sorted the table correctly or created appropriate indices. ... View more

How is the table DATABASE sorted? ... View more

I think the DDL to create the table happens before the query is actually performed. For some codes, that's a shortcut to duplicate the table structure without copying data. For example, if you say create table TABA as select * from TABB where 1=2; You will get a copy of the table structure without any data. I would think you'll have to use macros to check and see if there are records that match your criteria and only then would you create the table. ... View more

How about this? Proc SQL: update RO.CH_HIST_MASTER as t1 set ACAD_ORG = (Select NewOrg from RO_CAH_CONV where subject=t1.subject and catalog=t1.catalog_nbr and t2.group=t1.acad_group) where exists (select * from ro_cah_conv subject=t1.subject and catalog=t1.catalog_nbr and t2.group=t1.acad_group and strm='1390'); quit; ... View more

Just out of curiosity...why is an electronic pdf an acceptable historic document but the actual sas dataset not? ... View more

proc sql; select t1.year, t1.qtr, t1.sales, t2.ytd_sales from tablea as t1 inner join (select year, sum(sales) as ytd_sales from tablea group by year) as t2 on t1.year=t2.year; quit; ... View more