Thank You, From your code, every select query output created a new sheet in excel file, but I need 10 select quires under Country into one sheet and remain 10 select query output into another. (20 select statements create 20 sheets instead of 2 sheets). ... View more

In my second table (selected names) 2K + and in my first table more than 5 million records(this is depending on cycle wise) here need to find out how many selected names are available from first table. ... View more

When I used NAME Contains " ACADEMY " or it is ignoring the Leading and trail blanks For Ex:- 1. "CRICKETACADEMY" 2."CRICKETACADEMY UK" 3."CRICKETACADEMYUK" 4."CRICKET ACADEMYUK" 5."CRICKET ACADEMY UK" after running the code am getting 5. "CRICKET ACADEMY UK" only   Insted of this NAME Contains " ACADEMY " we have four possible patterns like this (with lead and, or trail blanks) NAME Contains "ACADEMY" or NAME Contains " ACADEMY" or NAME Contains " ACADEMY " or NAME Contains "ACADEMY " but it is taking number of iterations. Thank you. ... View more

Thank you, Actually here my requirement is 1. Name does not contain numbers, Selected Special characters. 2. Does not contain S/O, D/O, W/O and C/O ..... 3. Does not Single Token 4. At least one token having minimum 3 characters 5. Does not contain selected names (as mentioned above in Table 2) Here I need to run the 5 points in a single step and create invalid name dataset. Developed the code as per above requirement but i have confusion to get selected names. In my code for selected names I wrote like this. NAME Contains "ACADEMY" or NAME Contains "ACCCURED" or NAME Contains "ACCESSORIES" or NAME Contains "ACCOUNT" or . . . NAME Contains "ZINC" When I am running the same on millions of records it is taking huge time and systems are hanging. Thanks in Advance….! ... View more

For testing purpose run the quire on sample dataset. Observation count is 5316167 and am preparing the dataset for 30 millions. That's why I wondered  - IS IT OK......! here I am sharing the log. NOTE: There were 3433276 observations read from the data set WORK.TELEPHONE_1. NOTE: There were 1284869 observations read from the data set WORK.TELEPHONE_2. NOTE: There were 562389 observations read from the data set WORK.TELEPHONE_3. NOTE: There were 35406 observations read from the data set WORK.TELEPHONE_4. NOTE: There were 227 observations read from the data set WORK.TELEPHONE_5. NOTE: There were 0 observations read from the data set WORK.TELEPHONE_6. NOTE: There were 0 observations read from the data set WORK.TELEPHONE_7. NOTE: The data set WORK.PHONE has 5316167 observations and 3 variables. NOTE: DATA statement used (Total process time):       real time           1.37 seconds       cpu time            1.34 seconds Total Records -  WORK.PHONE has   5316167   observations and 3 variables proc sql; 90   create table dup as select distinct Telphoneno,fid from Phone 91   group by Telphoneno 92   having count(distinct  fid) gt 1 93   order by Telphoneno; NOTE: The query requires remerging summary statistics back with the original data. NOTE: Table WORK.DUP created, with 1334130 rows and 2 columns. 94   quit; NOTE: PROCEDURE SQL used (Total process time):       real time           35.28 seconds       cpu time            38.46 seconds suggest me any other alternatives to get it. ... View more

Thanks Xia Keshan, Run the above quire on 30 millions data, it is taken hardly not more than 10 min and I checked output randomly it was looking good. On the urgent basis I can apply this ... View more