Vish33 and manojinpec,   If the 'LEFT JOIN' you mentioned is the SQL LEFT JOIN, then you were wrong. SQL LEFT JOIN will keep the value of  first table instead. Regards, Haikuo ... View more

Not to answer Art, but to 'append' to the last. Thank you all, Cynthia, Art and Null. OK, I have got some walk-around suggestions to reduce my pain to somewhat manageable level. my original problem can be broke into 2 parts: 1. Too slow 2. Too big.  Of course they are related, big file takes long time to write. So the first suggestion I have got is targeting both, doing so, I have to ditch ODS. In brief, it is like this: Proc Export -- DDE + VBA macro for fancy formats. The second suggestion is lot simpler, but only targeting 'too big'. Flow will be like: ODS excelxp xml file -- open it and 'save as' xlsx file. It cuts down my 1.2G to 50M. Both approaches are doable using SAS (or call within SAS) , with no manual effort involved once setting up. I 'd appreciate your  comments and suggestions! Haikuo ... View more

Yep, that is probably why a warning window popping up when I channel tagsets.excelxp to .xls. See my response to Cynthia's post. Thanks, Haikuo ... View more

Cynthia, Thank you very much. That was really helpful in term of file size. It DOES zipped nicely! After zipping, my 1.2GB file shrank to 11M, it is like 1% compress ratio. I think I will go with zipping method when transferring data. I have tried .xls, it is smaller, but not significant enough to make an impact, also, it trigers a pop-up warning window when using Excel to open it, and believe it or not, some end users will be freaking out upon seeing that. I haven't tried them out yet, but I will ask them in general sense: Performance wise, given ODS must be used,  how do you feel if I use other output methods other than proc print? Such as proc report or data _null_? And I will contact tech support after I have exhausted my end of possibilities. Thanks again, Haikuo ... View more

Here is my log: 842     %macro hd_out; 843  ods listing close; 844  ods tagsets.excelxp 845     file='I:\temp\gap\gap_hd.xml' 846     style=xlsansprinter; 847  %do i=1 %to 3; 848  ods tagsets.excelxp 849      options (sheet_name="&&sheetname&i" 850               embedded_titles ='no' 851               autofilter = 'yes' 852               frozen_headers='1'); 853 854      proc printdata=code.hd_&i; 855      run; 856 857  %end; 858      odstagsets.excelxp close; 859  %mend; 860 861 862  %hd_out; MLOGIC(HD_OUT):  Beginning execution. MPRINT(HD_OUT):   ods listing close; MPRINT(HD_OUT):   ods tagsets.excelxpfile='I:\temp\gap\gap_hd.xml' style=xlsanspr NOTE: Writing TAGSETS.EXCELXP Body file:I:\temp\gap\gap_hd.xml NOTE: This is the Excel XP tagset (Compatible withSAS 9.1.3 and above, v1.116, 08 options(doc='help') to the ods statement for moreinformation. MLOGIC(HD_OUT):  %DO loop beginning; indexvariable I; start value is 1; stop valu       value is 1. SYMBOLGEN:  && resolves to &. SYMBOLGEN:  Macro variable I resolves to 1 SYMBOLGEN:  Macro variable SHEETNAME1resolves to HD Adquacy I-Monthly Measurement            Delivered HD Dose MPRINT(HD_OUT):   ods tagsets.excelxpoptions ( sheet_name="HD Adquacy I-Monthly M of Delivered HD Dose" embedded_titles ='no'autofilter = 'yes' frozen_headers='1') SYMBOLGEN:  Macro variable I resolves to 1 MPRINT(HD_OUT):   proc printdata=code.hd_1; MPRINT(HD_OUT):   run; NOTE: There were 255724 observations read from thedata set CODE.HD_1. NOTE: PROCEDURE PRINT used (Total process time):       realtime           26:41.15       cputime            26:40.64 MLOGIC(HD_OUT):  %DO loop index variable I isnow 2; loop will iterate again. SYMBOLGEN:  && resolves to &. SYMBOLGEN:  Macro variable I resolves to 2 SYMBOLGEN:  Macro variable SHEETNAME2resolves to HD Adquacy II-Method of Measurem            Delivered Dose MPRINT(HD_OUT):   ods tagsets.excelxpoptions ( sheet_name="HD Adquacy II-Method o Measurement of Delivered Dose"embedded_titles ='no' autofilter = 'yes' frozen_hea SYMBOLGEN:  Macro variable I resolves to 2 MPRINT(HD_OUT):   proc printdata=code.hd_2; MPRINT(HD_OUT):   run; NOTE: There were 367535 observations read from thedata set CODE.HD_2. NOTE: PROCEDURE PRINT used (Total process time):       realtime           38:10.41       cputime            38:09.70 MLOGIC(HD_OUT):  %DO loop index variable I isnow 3; loop will iterate again. SYMBOLGEN:  && resolves to &. SYMBOLGEN:  Macro variable I resolves to 3 SYMBOLGEN:  Macro variable SHEETNAME3 resolvesto HD Adquacy III-Minimum Delivered            Hemodialysis Dose MPRINT(HD_OUT):   ods tagsets.excelxpoptions ( sheet_name="HD Adquacy III-Minimum Hemodialysis Dose" embedded_titles ='no'autofilter = 'yes' frozen_headers='1'); SYMBOLGEN:  Macro variable I resolves to 3 MPRINT(HD_OUT):   proc printdata=code.hd_3; MPRINT(HD_OUT):   run; NOTE: There were 30981 observations read from thedata set CODE.HD_3. NOTE: PROCEDURE PRINT used (Total process time):       realtime           1:37.24       cputime            1:37.15 MLOGIC(HD_OUT):  %DO loop index variable I isnow 4; loop will not iterate again. MPRINT(HD_OUT):   ods tagsets.excelxpclose; MLOGIC(HD_OUT):  Ending execution. My test run only involved 3 tables, and it already takes me more than 1 hour. I am new to tagsets.excelxp syntax, so it is only normal I may have something in my code to drag slow the whole process. However, this time, I ended up with a xml file larger than 1Gb, and it takes excel more than 5 minutes just to load it. All this actually makes me think that tagset.excelxp is designed only for 'reporting' purpose instead of data outputting like this. Thank you all and any of your comments and suggestions will be much appreciated! Haikuo ... View more

More details: I used tagset.excelxp plus Proc print. I will post my code once the SAS finish this round, which may take another hour or 2. Thanks, Haikuo ... View more

More details would be needed for your question. There are so many types of random samples, just simple random sample, such as using ranuni(), or base on certain distributions? with or without replacement? For an official approach, please check out help documents on Proc Surveyselect, which is designed to do the sampling. Regards, Haikuo ... View more