BookmarkSubscribeRSS Feed
sas_Forum
Calcite | Level 5

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

5 REPLIES 5
Ksharp
Super User

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

sas_Forum
Calcite | Level 5

Please find the attached File

sas_Forum
Calcite | Level 5

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

Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 897 views
  • 0 likes
  • 2 in conversation