Anyways it's Reeza's solution... Worked fine... Thanks all! ... View more

nop, still having same warning message... i am assuming something needs to be changed in "copycmd" statement because i am having everything right until that last "DATA BOTH" step... Thanks! ... View more

Tom - below code is working fine except for "NJ" path. i am getting "copycmd" var exactly like mentioned below but not getting anything for NJ path in second "data both" step... -------------------------------------------------------------------------------------------------------------------------- /*i am not getting value for NJ path for this variable*/ copycmd copy "f:\woo\db\USUser1_market.mdb" "f:\transfer\mdb\ny\USUser1_market.mdb" copy "f:\woo\db\USUser2_market.mdb" "f:\mkt\files\pa\USUser2_market.mdb" copy "f:\woo\db\USUser3_market.mdb" "f:\transfer\mdb\ny\USUser3_market.mdb copy "f:\woo\db\USUser4_market.mdb" "f:\mkt\files\pa\USUser2_market.mdb" ------------------------------------------------------------------------------------------------------------------------ /*i have different path name for my original code but path contains same space, & and - (hyphen) signs for NJ path*/ getting this warning message: WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough          to contain the concatenation of all the arguments. The correct result would contain 203          characters, but the actual result may either be truncated to 200 character(s) or be          completely blank, depending on the calling environment. The following note indicates the          left-most argument that caused truncation. --------------------------------------------------------- proc format; value $ location                       "NY"="f:\transfer\mdb\ny"                       "PA"="f:\mkt\files\pa"                       "NJ"="f:\shares\market database\design & dev\business applications\finance - planning\nj"; run; %let path=f:\woo\db ; data files ;    infile "dir /b &path\*.mdb" pipe truncover lrecl=256 ;    input filename $256. ;    username = scan(filename,1,'_'); run; data both ;   merge files (in=in1) control (in=in2);   by username ;   if in1 ;   targetdir = put(location,$location.); run; data both ;   merge files (in=in1) control (in=in2);   by username ;   if in1 ;   targetdir = put(location,$location.);   copycmd = catx(' ','copy'                  ,quote(cats("&path\", filename))                   ,quote(cats(targetdir))              ); run; data _null_;     set both ;     infile cmd pipe filevar=copycmd ;     input @; run; ... View more

I am sorry if i have not provided enough info. but here it is; 8 same variables for both datasets atleast 9-10 millions of obs for each sas datasets both are sorted with key variable let's say, test_var For now i am using Tom's alternative - and really working good so far...but what i have in mind that PROC SQL would pulled query faster than DATA step but it seems proven wrong in this case, i am not sure if i missing anything...i have all same variables so i did rename for one dataset except one key variable, "test_var" and then merging as Tom mentioned here, it is giving me output faster than my above proc sql step...but i am preety sure we have some SQL alternative that we can use here that i am not aware of... Thanks! ... View more

nop... ... View more

Thanks Reeza, TOM, PGStats... Reeza - it worked grt...!!! ... View more

Tom - i did run it but giving so big output that is taking too much time open up. PGStats - query giving same constant value for Item I might not have represented my question very well i guess. i want: if Store "123456" has 3 Item (6578, 8766, 1256) and Sore "789101" has 5 item (6655, 2233,1467, 4545, 3333) then output would be like; Store       Item 123456    6578                8766                1256 789101    6655                2233                1467                 4545                 3333 Thanks! ... View more

check this if its help - we just had same kind conversation before couple of days... https://communities.sas.com/thread/52313 ... View more

- HJD24 - in your code - filesize variable is uninitialized. Thanks! ... View more

Thanks Tom and HJD24. meanwhile i have used different approch, i used proc export to get file in SAS and then used %sysfunc (open)/attrn/close options to get record and then email logic to send an email...this works fine for me... Thanks! ... View more

my file name is AB.WOOLAWSON.USWOO.DAT and this would be around .5 gb. sometimes it come with .2 gb which is much smaller and wants to create email alerts to users.... Thanks! ... View more

Thanks all for your information.... Thanks Tom for posting real code but when i put my values in your code without changing anything i am getting error like, "invalid do loop control information, either initial or to expression is missing or the by expression is missing, zero or invalid" Thanks! ... View more