extract a column from a excel files

[LineMoon]

Hello,

I have a many directories: D1,D2, ....Dn

Every directory Dj contains the  folder : Test, the folder Test contains the file : loc.xls.

The  loc.xls contains coluwn : col1, i want to keep the link to  Dk/Test where the column col1 contains only the zero .

Dj/Test/loc.xls

   file loc.xls

v1   v2    col1  b1  b2

1   2       0     8   9

3  9        0     11  3

I want to have

D2/Test

D7/Test

D13/Test

D130/Test

D326/Test

Thank you

[Reeza]

1. Use OS methods to list your directories -> Create a SAS table with the list of directories

2.  Create a macro to import the xls file and append to master data set

Both of these items are well documented on the forum.

[LineMoon]

Thank yo Reeza for your answer.

I do not want to import the xls files, because, I have so much.

I want to get the list without import

[Reeza]

Perhaps I'm missing something, how do you know which colums are 0 without reading file?

Edit: I suppose you can use a libname connection and not import but you'll still be connecting to each file.

[LineMoon]

We can see two cases :

Case one : we have an excel file with column : col1

Without reading the table, is it possible to use unix command to extract the table contains col1 ?   grep ?

  col1 

  0    

  0   

case 2: we have a sas file with column : col1, we want to extract the Dj/test who has a col1 with 0.

Do not forget that, I have so many files

[Reeza]

If you're looking for scripting options it may be better to check with a Unix forum, more people familiar with the options there.

[SASKiwi]

As far as I am aware Excel workbooks are stored in a proprietary format that cannot be analysed by any Unix command.

Also I don't follow your logic wanting to verify that column 1 in a spreadsheet contains 0 without "reading the table". You have to read in the Excel file in some form to be able to verify its contents.

If you access Excel from SAS using the EXCEL LIBNAME statement SAS is still reading the data even to create a temporary VIEW. This would one of the options I would recommend.