Import Excel with Differnt Ranges and Give the first obs as Datset Names

[sas_Forum]

I am having excel file i wnat to Read it a split and create new datasets when ever i reach all the columns are balnk

In this should create 5 datasets like ease,west,north,south,all zones and it should go to new dataaset
if it get all thecolumns as blank.All zones should have onely 1 Row it shd not read the data
after balnk as it shd not read Notes:

I am having differnt excel sheets with differnet zone names they are not fixed and range is also not fixed  and in some sheets i may have 8 zones and in some i will have 2 zones
and i shld give the zones as dataset names

I am attaching the Excel file with Data, and Text file for your Ref

Output

1.Datset East
zone city no
east 
mum anderi 123
zeharbad navi 345
kalyan new 456

2.Datset west
zone city no
west 
hyd nlg 456
secbad sec 1256

3.Datset North
zone city no
North 
ahmedabad ahm 23
nagur nng 456

4.Datset South
zone city no
south 
sahanari snr 345
gur gjk 256

5.Datset all_zones
zone city no 
All zones all 23569

[Ksharp]

I can't open your XLS file , It is corrupted .

[sas_Forum]

Please find the attached File

[Ksharp]

Still can't open .

[sas_Forum]

I have added the print screen and data in Data.txt please refer it .

[Ksharp]

libname x excel 'c:\data.xls' getnames=no mixed=yes;


proc sql noprint;
select quote(strip(name)),name
 into : name separated by ',', : name1 separated by ','
 from dictionary.columns 
  where libname='X' AND MEMNAME='Sheet1$';
quit;

data _null_;
if 0 then set x.'Sheet1$'n ;
dcl hash h(multidata:'y');
h.definekey(&name);
h.definedata(&name);
h.definedone();
do until (missing(cats(&name1)) or last);
 set x.'Sheet1$'n end=last;
 if not missing(cats(&name1)) then do;found=1; h.add();end;
end;
if found then do;n+1;h.output(dataset:cats('test',n)); end;
run;

Ksharp