Hi all Well, I have done some test and here are the results 1.Copy a dataset from oracle db to sas folder with a data-set statement 278  data a; 279  set b (keep = var1....var11); 280  IF var1 <= 20140530  and var2> 20140530 and var3>=20140530; run; NOTE: There were 56443158 observations read from the data set b. NOTE: The data set a has 53779976 observations and 11 variables. NOTE: DATA statement used (Total process time):       real time           35:12.97       user cpu time       7:00.68       system cpu time     2:03.58       Memory                            244k       OS Memory                         9072k       Timestamp            11/06/2014  9:43:12 1.Copy a dataset from oracle db to sas folder with a the pass-thru method 292  proc sql; 293  connect to odbc as Oracle (USER=zzzzzzz PW=XXXXXXXX DSN='wwwww'); 294  create table a as select * from connection to Oracle 295  ( 296  select var1,.....,var11 298  from b 299  where var1 <= 20140530  and var2> 20140530 and var3>=20140530; 300  quit; NOTE: Table a created, with 53779976 rows and 11 columns. NOTE: PROCEDURE SQL used (Total process time):       real time           36:14.66       user cpu time       7:24.60       system cpu time     2:05.03       Memory                            238k       OS Memory                         10864k 3 Copy a dataset from oracle db to sas folder with proc sql 312  proc sql; 313  create table a as 314  select var1,...var11 316  from b 317  where var1 <= 20140530  and var2> 20140530 and var3>=20140530; 318  quit; NOTE: Table a created, with 53779976 rows and 11 columns. NOTE: PROCEDURE SQL used (Total process time):       real time           35:22.28       user cpu time       6:49.44       system cpu time     1:57.15       Memory                            214k       OS Memory                         10352k As you can see the differences in time are minimal. On the other hand I have also tested the to sort with proc sort and with a hash object 1 proc sort   60 proc sort data = a out= b; 61 by var1; 62 run; NOTE: There were 7496967 observations read from the data set a. NOTE: The data set b has 7496967 observations and 44 variables. NOTE: PROCEDURE SORT used (Total process time): real time 4:27.56 cpu time 30.10 seconds 2. Hash object   73 data _null_; 74 if _n_=1 then do; 75 declare hash test (dataset:"sastemp.ec_dim_sub_con"); 76 test.definekey ('a'); 77 test.definedata ('a'); 78 test.definedone(); 79 call missing (a); 80 end; 81 rc = test.output (dataset:'sastemp.dummy'); 82 run; NOTE: There were 7496967 observations read from the data set a. NOTE: The data set b has 4676257 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 1:41.72 cpu time 10.56 seconds It looks very promising but when I put all the variables that I need to perform the join with the hash.... ERROR: Hash object added 2359280 items when memory failure occurred. FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase. ERROR: The SAS System stopped processing this step because of insufficient memory. And there are half the variables that I run with the proc sort. Finally I have to test the rachel's codes but Today the ftp server doens't work very well and I haven't download the files I need to and also because I think that I don't understand the code very well. I believe that is a join wich split in two and after union in one dataset, but I not very sure. By the way, what is the purpose of the call missing in the sort hash?. It's not clear for me. It is frustrating to have so much good advice and not be able to put into practice because my pc is a low end computer and I can't have access to create tables in the Oracle server too. I don't see a good alternative to reduce the time the join consume without take my home pc to the work best regards ... View more

  184 data _null_ ; 185 length Ckey $10; 186 if _N_=1 then do ; 187 declare hash test(dataset:"origin", ordered:'d'); 188 test.definekey ('CKey'); 189 test.definedata ('CKey'); 190 test.definedone(); 191 call missing ('CKey'); 192 end; 193 rc = test.output (dataset:'ouput'); 194 run; ERROR: Hash object added 18874352 items when memory failure occurred. FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase. ERROR: The SAS System stopped processing this step because of insufficient memory. NOTE: DATA statement used (Total process time): real time 1:47.61 user cpu time 36.50 seconds system cpu time 21.30 seconds Memory 1107591k OS Memory 1168676k Timestamp 10/06/2014 11:07:43 :smileyangry::smileyangry::smileyangry::smileyangry::smileyangry: I'm afraid I'm going to cry.... ... View more

rachel escribió: Sergio, Yes thats a problem not having enough disk space. You can try and assign a lib that's not the work directory, but some other location where you have more disk space. Other than that I dont know what you could do. I will try and think of work arounds. Sorry about that. Ok rachel, it's doesn't matter (thanks again for the help) but I thing you are wrong, in this case is a memory issue   not a problem with the disk space. ... View more

rachel escribió: I meant to say sort your two datasets using hash sort, because the hash sort is much faster then the proc sort. instead of proc sort, then try merging with a data step. data _null_ if _N_ =1 then do; declare hash h(dataset:"your_data", ordered:'d');  /* 'd ' means descending or you can put 'a' for ascending*/ h.defineKey('your_key'); h.defineData('your_key'); call missing(your_key); /*no quotes'*/ end; /* Create output dataset of sorted data */ rc=h.output(dataset:'out_your_data'); run; thanks rachel, it's look very nice, but what about the error in the previous hash? ERROR: Hash object added 25165808 items when memory failure occurred. FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase. ERROR: The SAS System stopped processing this step because of insufficient memory. Is there any option to avoid this error without having to change the pc? ... View more

I know it Jaap but it's my working computer and there is nothing I can do for change this machine or add more memory RAM which probably resolved the memory's problem. thanks bentleyj1 for the links I'll take a look. Tom, KurtBremser told me something like you are telling me now, I test it when I have a  little time . rachel, I don't kwow what you mean with you talk about hash sort. Could you explain to me please?. Regards. ... View more

Hi all I get an error when running the hash code   ERROR: Hash object added 25165808 items when memory failure occurred. FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase. ERROR: The SAS System stopped processing this step because of insufficient memory. My PC info: CPU :    Intel(R) Core(TM) i3-2100 CPU @ 3.10GHz Available Memory : 3242 MB ---------------------------------------------------- Drive List : Drive C: Type: Fixed Total available space:         79 GB Total size of drive:            232 GB Thanks ... View more

rachel escribió: Sergio, I created  two fake data sets. One with 50mil rows, the other with 30 mil rows. Then I merge then. Remember in a hash join for large data place the smalled data on top. Here is the code; ****************************************** Thanks a lot rachel ... View more

Thanks all  for you tips, I'll keep in mind rachel escribió: Sergio, You can try a hash join, it runs a lot quicker. If you want I can post an example. -raisa Sure  raisa, please go on Regards ... View more

Well, another option, I am downloading the table order by thee key  variable using pass thourh. After this I'll downloading the second table, ordering too. This way I suppose I dont need the proc sort and I can perfom a merge to find wich rows are in common. What do you think about this? Thanks ... View more

Jaap Karman escribió: Sergio, Jwillis has used an installation with an oracle client (schema=). That one is offering more advanced options. The oracle client is a free client from oracle but is requiring a SAS license for SAS/Access to Oracel. You are using the ODBC method obviously using the Windows client (DSN= ) Within a DBMS (explicit pass through) there is a common separation by schema-s. This looking like the libnames in SAS. You cannot intermix libnames (SAS environment) and schemas (DBMS environment). The important difference imo is " as  select G.*"   between the create and from connection Sorry Jaap but I don't understand anything, I run the following code and it works perfectly proc sql; connect to odbc as aaaaaa(dsn=xxxx USER=xxxxx PW=xxxxxx); create table test as select * from connection to aaaaaa (select * from schemaname.tablename); quit; After the code run I can see the dataset in my library, what is the difference between this code and the join I try to execute? Regards ... View more

Morning all jwillis your code doesn't work for me, it`s neither an alias issue or were conditions.:smileycry::smileycry: I'll see if I am able to find a solution. Thanks ... View more

Sorry guys, my PC have been half to death all day and I couldn´t test your adviced. I'm trying to sent a the query to Oracle server but I recive an error like this "ERROR: PROC SQL requires any created table to have at least 1 column." 96   options fullstimer; 97   options sastrace=',,,s ,,d, ,,t,' sastraceloc=saslog nostsuffix; 98 99   proc sql; 100  connect to odbc as oracle (USER=aaaaaa PW=XXXXXXXX DSN='pppp'); 101  create table result as select * from connection to oracle 102  (select a.var1, a.var2, a.var3, a.var4, a.var5, 102  a.var6, a.var7, a.var8, a.var9, a.var10, b.var1, b.var2 104  from epi as a left join blas b on (a.var1 = b.var1) 105  where a.var1 <= date and a.var2 >= date and 105! a.var3>date); ERROR: PROC SQL requires any created table to have at least 1 column LinusH, both datasets are in the work library, I copy it  from a DWH in Oracle server. Thanks for the link Jaap, It's very helpful for me. One more thing, I haven`t permissions for create, modify or alter tables in the Server so all the tables that I create should be in the local drive Regards ... View more

Morning all First, thanks for the help it´s much appreciated for me. Well, I´ll try to answer your questions. The data are in an Oracle Server and I make a copy of the datasets connecting to the server through a libname statement and after data a; set b (where = (var1<= date and var2>date and var3>date); where "b" is the dataset on the Oracle server I do a left join, the target in most of the cases is to obtain the surrogate key of the second table and one or two variables more.Something like this proc sql; create table x as select a.*, b.var1, b.var2, b.var3 from dataset1 as a left join dataset2 as b on (a.var1 = b.var1); quit; There is no index at all in any of the datasets and no message appears in the log about an issue in the performance Regards ... View more