Don't put the file names into macro variables. Put them into a dataset.  A macro variable has a maxium length of 64K.  Even a moderate number of names could easily overflow that limit.   There is a great idea for doing the recursion into the subdirectories by using the MODIFY statement in a data step posted in this thread: https://communities.sas.com/t5/SAS-Programming/listing-all-files-within-a-directory-and-subdirectories/m-p/332616/highlight/true#M74887   Here is an example.  First create a dataset with the structure you want and one observation for each top level directory you want to read. data filelist; length root filename $256 dir level 8; input root; retain filename ' ' level 0 dir 1; cards4; c:\temp ;;;; Now run this data step to scan all of the files in the directory(s). data filelist; modify filelist; rc1=filename('tmp',catx('\',root,filename)); rc2=dopen('tmp'); dir = not not rc2; replace; if dir; level=level+1; filename0=filename; do i=1 to dnum(rc2); filename=catx('\',filename0,dread(rc2,i)); output; end; rc3=dclose(rc2); run; If you want to limit to files with a particular extension it is probably easier after you have the dataset already. proc print data=filelist; where filename like '%.sas7bdat'; run; Results Obs root filename dir level 4 c:\temp one.sas7bdat 0 1 6 c:\temp two.sas7bdat 0 1 7 c:\temp sub1\three.sas7bdat 0 2 ... View more

data have; input var1 col1 $ col2 $ col3 $ col4 $; datalines; 0 asfds sdfsg sdfsf sdfsdg ; proc transpose data=have out=want(rename=(_name_=colname col1=valueincol) drop=var1); by var1; var col1-col4; run; Result:   colname valueincol col1 asfds col2 sdfsg col3 sdfsf col4 sdfsdg ... View more

I'm with @SuryaKiran that if you overwrite the source data set with an ordered and deduped version then someone else might be using this already sorted data  - and that could explain different results.   Looking at the code you've posted and that you just want unique rows per var1, var2 I can't see any reason to use a SQL distinct of more variables first. That just adds additional processing. You should get consistent results with less passes through the data and less sorting by using below code: proc sort nodupkey data=have out=have_sorted ; by var1 var2; run; ... View more

data have; input string $; datalines; abc234 123abc a2c123 fghfwd Ads23d BS2Csd run; data want; set have; if prxmatch('/[^a-z]/i', substr(string,1,3)) then delete; run; ... View more

This will fix flags for table 1. From reading your question again, I am not sure for table 2 and 3 how the data will join without seeing it or if I am understanding the question correctly.   proc sql; drop table main; create table main( edid char(20) ); insert into main values ('dup1') values ('dup2') values ('dup1') values ('dup1') values ('dup3') ; quit; proc sort data=main; by edid; run; data table1; set main; count + 1; by edid; if first.edid then do; count = 1; flag_t1=1; end; else flag_t1 = 0; run; ... View more