topic Re: Importing specific Excel .xlsx rows in SAS Programming

Importing specific Excel .xlsx rows

copony — Sun, 27 Jul 2014 21:26:45 GMT

I posted this in the statistical procedures section by accident, but I think it is more appropriate here. I'm new to the board. I apologize for the duplicate posts.

I'm relatively new to the DATA and PROC IMPORT steps of SAS (am more statistics oriented and have not managed importing data into SAS, specifically) and I'm wondering if there is a way to import specific rows into SAS. The problem with my Excel sheets is that they contain a lot of data in rows that I don't need. Also, the first line of the Excel sheet is not a list of variable names (I didn't design these spreadsheets, obviously!).

Specifically, this is what I need to do:

Import cell B2 (this is study ID#) and then import A10, B10 and A11, B11. Then I would like B12 - Y12 (these are time variables) and then B24 - Y24 (the values that correspond to B12-Y12 variables).

Anything in an A cell is a variable, but I don't necessarily NEED it as I could figure out what variable I'm looking at later on.

There are multiple worksheets in each workbook, and I am willing to read each workbook/worksheet in by hand if necessary.

Ordinarily I would run a macro/VBA in Excel to get this info but our security policies are such that we aren't allowed to run macros in Excel or Access!

All suggestions appreciated!

Re: Importing specific Excel .xlsx rows

PGStats — Mon, 28 Jul 2014 03:23:23 GMT

Assuming each part of your data has a uniform data type (character or numeric), you can read all the parts (ranges) in separate datasets with the LIBNAME Engine :

libname xl Excel "&sasforum\datasets\copony.xlsx" header=no access=readonly;

proc sql;

create table id as select F1 as ID from xl.'Sheet1$B2:B2'n;

create table A10B10 as select F1 as A10, F2 as B10 from xl.'Sheet1$A10:B10'n;

create table A11B11 as select F1 as A11, F2 as B11 from xl.'Sheet1$A11:B11'n;

create table B12Y12 as select * from xl.'Sheet1$B12:Y12'n;

create table B24Y24 as select * from xl.'Sheet1$B24:Y24'n;

quit;

libname xl clear;

When you read Excel data without column names (header=no) the variables take the names F1, F2, etc.

You can then combine the parts (SAS datasets id, A10B10, A11B11, B12Y12, B24Y24) with other SQL or data steps.

Re: Importing specific Excel .xlsx rows

copony — Wed, 30 Jul 2014 15:59:46 GMT

Thank you very much PGStats - the import worked. The only thing is that the data in cells B12-Y12 is 24 hour style time (e.g., 0:00, 1:00, 2:00 through 24:00). The time converts to "30Dec1899" for all the values in the SAS Table. Do you know of a code to transform this back into time points?

Re: Importing specific Excel .xlsx rows

FriedEgg — Wed, 30 Jul 2014 16:13:56 GMT

You can do this at the input stage using the dbsastype option.

proc sql;

create table b12y12 as select * from xl.'Sheet1$B12:Y12'n (dbsastype=(b12="time" ...));

quit;

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 30 Jul 2014 16:53:57 GMT

It might be less trouble to use libname options telling SAS to watch for time values:

libname xl Excel "&sasforum\datasets\copony.xlsx" header=no access=readonly usedate=yes scantime=yes;

proc sql;

create table id as select F1 as ID from xl.'Feuil1$B2:B2'n;

create table A10B10 as select F1 as A10, F2 as B10 from xl.'Feuil1$A10:B10'n;

create table A11B11 as select F1 as A11, F2 as B11 from xl.'Feuil1$A11:B11'n;

create table B12Y12 as select * from xl.'Feuil1$B12:Y12'n;

create table B24Y24 as select * from xl.'Feuil1$B24:Y24'n;

quit;

libname xl clear;

Re: Importing specific Excel .xlsx rows

copony — Wed, 30 Jul 2014 17:01:28 GMT

@FriedEgg, when I input exactly as you wrote I get "ERROR: Invalid syntax for option DBSASTYPE'

I also tried inputting without the ellipses and got "ERROR: Invalid column name specified DBSASTYPE option: b12'

Am I omitting something?

Re: Importing specific Excel .xlsx rows

copony — Wed, 30 Jul 2014 17:08:43 GMT

Awesome , that was the trick! Thanks so much!

Now, since there are multiple worksheets, how do I combine all the tables so I can get one table of all the values?

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 30 Jul 2014 17:15:30 GMT

Well, first, how do you want the 5 ranges extracted from a single worksheet to be combined into a dataset? - PG

Re: Importing specific Excel .xlsx rows

copony — Wed, 30 Jul 2014 17:33:01 GMT

Well...

--B12Y12 is the clock time 00:00 through 24:00 and it is consistent throughout all sheets

--B2 is a unique ID specific to the following variables:

--A10:B10 is variable1:string value for variable 1

--A11:B11 is variable2:string value for variable 2

--B24:Y24 are numeric values that correspond to the time in B12:Y12

--B25:Y25 are numeric values that correspond to the time in B12:Y12

--B35:Y35 are numeric values that correspond to the time in B12:Y12

There are roughly 100 unique IDs (identified in B2 in each sheet), each ID has an average of 3 corresponding worksheets of data. Each sheet has the same layout, but it represents a different day of values recorded for the unique ID. Did I explain this clearly? Please let me know if I can clarify further.

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 30 Jul 2014 17:44:57 GMT

How can you tell apart the dates corresponding to a given ID?

Re: Importing specific Excel .xlsx rows

copony — Wed, 30 Jul 2014 18:05:58 GMT

B10 in each sheet is actually a date/time it's a string value due to the way it was entered in the original Excel worksheet, example: 21October2010/2345.

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 30 Jul 2014 18:34:01 GMT

I think something like this should work.

/* Define a macro to read one worksheet */

%macro getdata(file,ds);

libname xl Excel "&sasforum\datasets\&file..xlsx" header=no access=readonly usedate=yes scantime=yes;

proc sql;

create table id as select F1 as ID from xl.'Sheet1$B2:B2'n;

create table A10B10 as select catx(" ", F1, F2) as date from xl.'Sheet1$A10:B10'n;

create table A11B11 as select catx(" ", F1, F2) as var2 from xl.'Sheet1$A11:B11'n;

create table t as

select * from xl.'Sheet1$B12:Y12'n

union all

select * from xl.'Sheet1$B24:Y24'n

union all

select * from xl.'Sheet1$B25:Y25'n

union all

select * from xl.'Sheet1$B35:Y35'n;

quit;

libname xl clear;

proc transpose data=t out=tl; run;

proc sql;

create table &ds as

select

id,

date,

var2,

col1 as t,

col2 as val1 format=best.,

col3 as val2 format=best.,

col4 as val3 format=best.

from id, A10B10, A11B11, tl;

drop table id, A10B10, A11B11, tl, t;

quit;

%mend;

/* Call the macro */

%getdata(copony,ds001);

/* ... repeat for all data files. Give all output datasets names starting with ds */

/* Assemble all datasets that have names starting with ds */

data allDs;

set ds:;

run;

Re: Importing specific Excel .xlsx rows

copony — Wed, 03 Sep 2014 17:38:06 GMT

Thanks for your code (sorry for my delayed response - I had a death in my family last month and am just getting back into this SAS project) conceptually it seems sound but I'm getting many errors... can you (or anyone) help me figure out why I'm getting so many errors when I run the code? Errors are as follows:

65 from id, A10C10, A11B11, tl;

67 drop table id, A10C10, A11B11, tl, t;

69 quit;

71 %mend;

75 /* Call the macro */

77 %getdata(copony,ds001);

NOTE: Libref XL was successfully assigned as follows:

Engine: EXCEL

Physical Name: "&sasforum\datasets\&file.xlsx

NOTE: Table WORK.ID created, with 1 rows and 1 columns.

NOTE: Table WORK.A10C10 created, with 1 rows and 1 columns.

NOTE: Table WORK.A11B11 created, with 1 rows and 1 columns.

ERROR: Column 1 from the first contributor of UNION ALL is not the same type as its counterpart

from the second.

ERROR: Column 2 from the first contributor of UNION ALL is not the same type as its counterpart

from the second.

ERROR: Column 3 from the first contributor of UNION ALL is not the same type as its counterpart

from the second.

(and so on through row 24...)

and:

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 2.16 seconds

cpu time 0.43 seconds

NOTE: Libref XL has been deassigned.

ERROR: File WORK.T.DATA does not exist.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TL may be incomplete. When this step was stopped there were 0

observations and 0 variables.

NOTE: PROCEDURE TRANSPOSE used (Total process time):

real time 0.14 seconds

cpu time 0.04 seconds

ERROR: Table WORK.TL doesn't have any columns. PROC SQL requires each of its tables to have at

least 1 column.

ERROR: Character expression requires a character format.

ERROR: The following columns were not found in the contributing tables: col1, col2, col3, col4.

NOTE: Table WORK.ID has been dropped.

NOTE: Table WORK.A10C10 has been dropped.

NOTE: Table WORK.A11B11 has been dropped.

NOTE: Table WORK.TL has been dropped.

WARNING: File WORK.T.DATA does not exist.

WARNING: Table WORK.T has not been dropped.

NOTE: The SAS System stopped processing this step because of errors.

And finally:

87 set ds:;

ERROR: The data set list (WORK.ds:) does not contain any members.

89 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.ALLDS may be incomplete. When this step was stopped there were 0

observations and 0 variables.

NOTE: DATA statement used (Total process time):

real time 0.51 seconds

cpu time 0.04 seconds

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 03 Sep 2014 17:52:33 GMT

Welcome back. Sorry for your loss.

&sasforum\datasets\ isn't a path that is meaningful in your envirionment. It is defined on my machine to point to a particular folder. You should replace it with the location of your xlsx files; something like

libname xl Excel "C:\temp\&file..xlsx" ...

Re: Importing specific Excel .xlsx rows

copony — Wed, 03 Sep 2014 17:59:38 GMT

thanks for responding so quickly!

&sasforum\datasets\isn't a path that is meaningful in your envirionment. It is defined on my machine to point to a particular folder. You should replace it with the location of your xlsx files; something like

libname xl Excel "C:\temp\&file..xlsx" ...

Yes - I definitely know this and had replaced that path with one that is meaningful in my environment - I just didn't want to post it on a public forum :-). Even with the correct path, I still got all those errors...

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 03 Sep 2014 18:24:33 GMT

Ok then, Read a single workbook and check that the types (character or number) of corresponding variables in datasets B12Y12, B24Y24, B25Y25 and B35Y35 all match. The error message says they don't, which makes the fields impossible to combine.

Re: Importing specific Excel .xlsx rows

copony — Wed, 03 Sep 2014 18:35:44 GMT

Some of the corresponding values for the variables are missing. Could that be the problem?

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 03 Sep 2014 18:52:22 GMT

Yes it could. As far as I can tell (this is not documented) the type of missing values in Excel is determined by their format. Missing values without a format as assumed to be character. Whether you want to correct the workbooks or fix the problem by programming depends on your patience and the number of workbooks involved. In other words, you could assign formats to Excel ranges or do some fancy footwork in SAS.

Re: Importing specific Excel .xlsx rows

copony — Wed, 03 Sep 2014 18:56:42 GMT

I think I might have to use SAS, however complicated, because I can't run macros in Excel due to our security policies and there are many workbooks containing multiple worksheets. Where should I begin in SAS?

Re: Importing specific Excel .xlsx rows

PGStats — Wed, 03 Sep 2014 21:38:32 GMT

OK. Here is a new version that should tolerate the presence of character variables in the imported ranges. They are transformed into missing values.

%macro getdata(file,ds,Sheet);

libname xl Excel "&sasforum\datasets\&file..xlsx" header=no access=readonly usedate=yes scantime=yes;

data B12Y12;

set xl."&Sheet.$B12:Y12"n;

keep _NUMERIC_;

run;

data B24Y24;

set xl."&Sheet.$B24:Y24"n;

keep _NUMERIC_;

run;

data B25Y25;

set xl."&Sheet.$B25:Y25"n;

keep _NUMERIC_;

run;

data B35Y35;

set xl."&Sheet.$B35:Y35"n;

keep _NUMERIC_;

run;

proc sql;

create table id as select F1 as ID from xl."&Sheet.$B2:B2"n;

create table A10B10 as select catx(" ", F1, F2) as date from xl."&Sheet.$A10:B10"n;

create table A11B11 as select catx(" ", F1, F2) as var2 from xl."&Sheet.$A11:B11"n;

quit;

libname xl clear;

/* Empty dataset to ensure the presence of all 24 variables F1-F24 */

data dum; array F[24]; stop; run;

data t;

set dum B12Y12 B24Y24 B25Y25 B35Y35;

run;

proc transpose data=t out=tl; var f1-f24; run;

proc sql;

create table &ds as

select

id,

date,

var2,

col1 as t ,

col2 as val1 format=best.,

col3 as val2 format=best.,

col4 as val3 format=best.

from id, A10B10, A11B11, tl;

drop table id, A10B10, A11B11, dum, B12Y12, B24Y24, B25Y25, B35Y35, tl, t;

quit;

%mend;

option mprint; /* for testing */

%getdata(copony,ds001,Sheet1);

/* ... repeat for all worksheets. give all output datasets names starting with ds */

/* Assemble all datasets that have names starting with ds */

data allDs;

set ds:;

run;