topic Re: extract a column from a excel files in SAS Programming

extract a column from a excel files

LineMoon — Sat, 18 Jul 2015 10:17:41 GMT

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

Re: extract a column from a excel files

Reeza — Sat, 18 Jul 2015 16:50:04 GMT

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.

Re: extract a column from a excel files

LineMoon — Sat, 18 Jul 2015 16:56:10 GMT

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

Re: extract a column from a excel files

Reeza — Sat, 18 Jul 2015 17:47:34 GMT

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.

Re: extract a column from a excel files

LineMoon — Sun, 19 Jul 2015 00:03:52 GMT

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

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

Re: extract a column from a excel files

Reeza — Sun, 19 Jul 2015 00:36:43 GMT

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

Re: extract a column from a excel files

SASKiwi — Sun, 19 Jul 2015 00:46:34 GMT

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.