topic Re: Import Excel with Differnt Ranges and Give the first obs as Datset Names in SAS Programming

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

sas_Forum — Wed, 16 May 2012 06:49:33 GMT

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

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

Ksharp — Wed, 16 May 2012 07:12:06 GMT

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

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

sas_Forum — Wed, 16 May 2012 07:32:09 GMT

Please find the attached File

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

Ksharp — Wed, 16 May 2012 07:56:36 GMT

Still can't open .

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

sas_Forum — Wed, 16 May 2012 08:01:59 GMT

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

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

Ksharp — Fri, 18 May 2012 02:34:02 GMT

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