Hello @Tom, thanks a lot, for this elegant solution. Also, thanks for the elaboration on the LENGTH / FORMAT statement. I do have a follow-up question, however: Am I assuming correct, that this line of your code   input (ID -- VAR4) (:null.) ; assigns the created invlaue checks ("null" and "$null")?   How does SAS know when to use the invalue-check without the $ sign for numbers variables and when to use the one with the $ sign for the character variables?     ... View more

I checked it. It is turned on, i.e. I can send x commands from within SAS-EG:   ... View more

Hi all, I found another bug in the HASH-method:   TAB2.definedata(....). Therefore, I am posting my code, which ran perfectly fine without any warnings or errors:   %macro g; %let cols=1 5 10 20 30 40 50; %let rows=0.01 0.05 0.10 0.20 0.50 1; %do colno=1 %to %sysfunc(countw(&cols)); %let colnb=%scan(&cols,&colno); %do rowno=1 %to %sysfunc(countw(&rows,%str( ))); %let rowpct=%scan(&rows,&rowno,%str ( )); %put Trace: &=rowpct &=colnb *start; data TAB1 TAB2; retain VAR1-VAR50 8; do I=1 to 1e7; KEY=int(ranuni(0)*1e15); output; end; run; %put Trace: &=rowpct &=colnb *sort merge; proc sort data=TAB1 out=SORTED1 sortsize=1g details; by KEY; run; proc sort data=TAB2(keep=KEY VAR1-VAR&colnb.) out=SORTED2 sortsize=1g details; by KEY; run; data OUT; merge SORTED1 SORTED2; by KEY; %* We always fetch all rows with a merge; run; %put Trace: &=rowpct &=colnb *index; proc sql; create index KEY on TAB2; data OUT; set TAB1; if ranuni(0) < &rowpct. then set TAB2(keep=KEY VAR1-VAR&colnb.) key=KEY; run; %put Trace: &=rowpct &=colnb *index sasfile; data REDUCED(index=(KEY)); set TAB2(keep=KEY VAR1-VAR&colnb.); run; sasfile REDUCED open; data OUT; set TAB1; if ranuni(0) < &rowpct. then set REDUCED(keep=KEY VAR1-VAR&colnb.) key=KEY; run; sasfile REDUCED close; %put Trace: &=rowpct &=colnb *hash; data OUT; set TAB1; if _N_=1 then do; dcl hash TAB2(dataset: "TAB2(keep=KEY VAR1-VAR&colnb)" ); TAB2.definekey('KEY'); TAB2.definedata('KEY'%do j=1 %to &colnb.;,%unquote(%nrbquote("VAR&j."))%end; ); TAB2.definedone(); end; if ranuni(0) < &rowpct. then RC=TAB2.find(); run; %end; %end; %mend; Cheers, FK1 ... View more

Hello @ChrisNZ,   thank you for your very interessting article. As you mentioned yourself: "It is even possible to use other SAS features such as formats." I was wondering: have you also tried the "format" method?   On first sight, I found there is for example a "r" missing in the word "run" in the second proc sort step:   proc sort data=TAB2(keep=KEY VAR1-VAR30&colnb.) out=SORTED2 sortsize=1g details; by KEY; un;  Also, you must have typed accidentaly a "30": VAR30&colnb.     ERROR: Not all variables in the list VAR1-VAR301 were found. 180: LINE and COLUMN cannot be determined. NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 180-322: Statement is not valid or it is used out of proper order. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.SORTED2 may be incomplete. When this step was stopped there were 0 observations and 0 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.02 seconds cpu time 0.00 seconds     Sincerely yours, FK ... View more

You can use the "Finfo" (=file information item) function which is offered by SAS:   data info; length infoname infoval $60; drop rc fid infonum i close; rc=filename('abc','physical-filename'); fid=fopen('abc'); infonum=foptnum(fid); do i=1 to infonum; infoname=foptname(fid,i); infoval=finfo(fid,infoname); output; end; close=fclose(fid); run; Depending on the Operating System (Windows, UNIX, z/Os, etc.) you get some information bits about your DataSet.    For more information: http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p0cpuq4ew0dxipn1vtravlludjm7.htm   Cheers, FK   ... View more