Alternatively, you can use CALL SYMPUTX in data step, which passes your decimals and long numeric values (up to 17 I believe) into macro variable without truncation.  data test; x=16017854.36; call symputx('x', x); run; %put &=x; log: X=16017854.36 ... View more

We need to see the complete log of the step that does the job (or the steps, if it's done with SORT and MERGE). ... View more

Thanks for the solution. I would also like to include your post as a solution but I don't think it's possible to put more than one solution ... View more

Yes. And you can DROP rc also. ... View more

@mariopellegrini - I don't think there is an ideal number for everyone. I usually try a few different values, like 1,000, 2,000, 5,000, 10,000, 15,000, 20,000 to see which gives the best performance. We currently use 10,000 for most of our loads and that works well. There is no point using a larger number than the number of rows you are appending though. ... View more

  Use the COMPRESS=YES system option (or COMPRESS=YES dataset option if you only want it to apply to one dataset) and it will take a lot less space on the disk.   Why attach a spreadsheet to share 8 numbers?   memname  nobs   obslen   nvar   compress   filesize  RUOLO_DANNI1   67,298,471        136      12  NO      9,169,469,440 RUOLO_DANNI2   67,298,471        336      13  NO    22,734,503,936 ... View more

Best way to improve performance is not to do cartesian product of too large tables.   If you have the data sorted by the key variables and you just want to do a normal one-to-one or one-to-many merge then a data step will be much faster than anything you could build with SQL. data want ; merge ore(in=in1) aa(in=in2 keep=k value1); by k; if in1 and in2; run; Whether or not that is possible depends on what you are actually trying to do. ... View more

OK, I agree. However, out of curiosity, I would like to understand if it is possible to arrive at an exact conversion and solve question n.1 concerning the filter on "ts_beginning_validity". I don't understand why it doesn't work ... View more

Kurt  I found some data inconsistencies between sql and hash, because the "ON" condition is different: ts_start_validation is taken from the left dataset, while ts_start_validation  ts_end_validation of the "beetween" are taken from the right dataset. This example explains the problem with a particular case ((the results of ruolo_danni_sql and ruolo_danni_hash  are different): data RUOLO_DANNI; input cod_source:$2. cod_anagrafico:$9. ts_inizio_validita:datetime20.; datalines; 1 21278 02JUL11:03:25:44 1 21278 02JUL11:03:25:45 1 21278 02JUL11:03:25:46 ; data T_A_SOGGETTI_DN; input cod_source:$2. cod_anagrafico:$9. cod_anagrafico_edwh:$20. ts_inizio_validita:datetime20. ts_fine_validita:datetime20.; datalines; 1 21278 F_SCDDNL64D45Z133D 01JUL2011:11:15:02 01JUL2011:11:15:01 ; proc sql noprint; create table ruolo_danni_sql as select RU.cod_anagrafico, RU.ts_inizio_validita, RU.cod_source, SO.cod_anagrafico_edwh from RUOLO_DANNI RU left join T_A_SOGGETTI_DN SO on RU.cod_anagrafico = SO.cod_anagrafico and ru.ts_inizio_validita between so.ts_inizio_validita and so.ts_fine_validita and compress(SO.cod_source)='1'; quit; data ruolo_danni_HASH; set ruolo_danni; if _n_ = 1 then do; length ts_inizio_validita ts_fine_validita 8; length cod_anagrafico_edwh $20.; declare hash so (dataset:"t_a_soggetti_dn (where=(compress(cod_source)='1'))"); so.definekey("cod_anagrafico"); so.definedata("cod_anagrafico_edwh","ts_inizio_validita","ts_fine_validita"); so.definedone(); call missing(cod_anagrafico_edwh, ts_inizio_validita,ts_fine_validita); end; rc = so.find(); if rc >= 0 and ts_inizio_validita le ts_inizio_validita le ts_fine_validita; drop rc ; run;   ... View more

Move the KEEP= option to the input dataset Use TAGSORT only if you have a heavily COMPRESSed, large dataset Use COMPRESS=YES on the output dataset if it will contain large character variables which are mostly empty ... View more

the SYSLCKRC variable gives me a value greater than zero even keeping the table open. with the lock lib.member list statement I can't "capture" the value obtained in a variable... ... View more

Are you able to post the actual log (not just the notes on the log) from the test you ran?     I understand you may not be able to do that because of confidentiality reasons, but post what you can. ... View more

I think this should work: data ds_tot_hash; if 0 then set ds1 ds2; if _N_=1 then do; declare hash h_merge(dataset:"ds2", multidata:'y' ); rc = h_merge.DefineKey("k1"); rc = h_merge.DefineData("ts_start", "ts_end"); rc = h_merge.DefineDone(); end; found=0; set ds1 (keep=k1 ts_start rename=(ts_start=a_start)) end=eof; rc=h_merge.find(); do while(rc=0); if ts_start<=a_start<=ts_end then do; found=1; output; end; rc=h_merge.find_next(); end; if found=0 then do; call missing(ts_start,ts_end); output; end; keep k1 ts_start ts_end; run; We have to include the FOUND variable, to check if something has been output, in order to simulate the left join.   Only question: is this really worth the trouble? ... View more