topic Re: Read in Excel data into SAS: variable labels in second row in SAS Programming

Read in Excel data into SAS: variable labels in second row

Mandy1 — Sun, 07 Apr 2013 15:14:26 GMT

Hello,

I have two questions about reading in excel data(C:\Users\mandy\Downloads\model1.xls) into SAS.

Varaible names are in the first row of the excel file; varaible labels are in the second row. It looks like this:

-------------model1.xls--------

Year ID wage hours

year ID hourly wage annual work hours

2010 1 30 4800

2010 2 21 4100

...............

------------------------

(1) The codes I used are as follows:

------------

import out=model1

datafile="C:\Users\mandy\Downloads\model1.xls"

dbms=xls replace;

getnames=yes;

run;

-----------

The error massage I got is:

ERROR: Physical file does not exist, /sso/biconfig/Lev1/SASApp/C:\Users\mandy\Downloads\revenue model data.xls.

I think the problem is caused by "/sso/biconfig/Lev1/SASApp/" before the location of the excel file. Any suggestion on how to refer to the correct location of the excel file?

(2) Is there any way to read into the varible labels easily?

Thank you!

Mandy

Re: Read in Excel data into SAS: variable labels in second row

data_null__ — Sun, 07 Apr 2013 16:04:59 GMT

Looks like your SAS session is executing on a UNIX server. You will need to point to the file using the proper syntax. You may also need to copy the file to a different location if UNIX cannot "see" your "C:" which seems likely.

Re: Read in Excel data into SAS: variable labels in second row

RichardinOz — Sun, 07 Apr 2013 23:15:22 GMT

Once you sort out the file location issue and you can import the data you will find that SAS assigns each column to a character variable because of the second row. At that point you can create a process to set up the variable labels in a new table using just the second row, and another table to contain the transformed numerical values (firstobs=2), then append the two.

Or you might find it easier to create a tab delimited output file from Excel, upload it to Unix and read it twice, once for the first 2 rows to get variable labels and then a second time to import the values.

If all your data is numeric the easiest way to apply the labels to variable names is to create a table from the first 2 rows, then transpose it, keeping Col1 which you rename _LABEL_, adding a dummy numerical value in a data or SQL step, and then transposing back.

Richard

Re: Read in Excel data into SAS: variable labels in second row

Mandy1 — Mon, 08 Apr 2013 00:50:48 GMT

Thank you for the input!

Re: Read in Excel data into SAS: variable labels in second row

RichardinOz — Mon, 08 Apr 2013 03:29:00 GMT

If you decide to use the tab delimited approach (safer than a CSV) let me know and i might be able to be more specific with code.

Richard

Re: Read in Excel data into SAS: variable labels in second row

Mandy1 — Mon, 08 Apr 2013 03:51:16 GMT

Hello Richard,

I would be happy to know the codes to read a tab deliminated data file (first row variable names; second row variable labels) into SAS. Thank you for your help!

Mandy

Re: Read in Excel data into SAS: variable labels in second row

RichardinOz — Mon, 08 Apr 2013 07:35:29 GMT

I'll do my best. I don't have access to a functioning version of SAS so you might need to change a few things.

Assuming you have uploaded your tab delimited file to

/sso/biconfig/Lev1/SASApp/model1.txt

Filename model1 '/sso/biconfig/Lev1/SASApp/model1.txt' ;

/*Split into 2 text files */

Filename values '/sso/biconfig/Lev1/SASApp/model1values.txt' ;

Filename labels '/sso/biconfig/Lev1/SASApp/model1labels.txt' ;

Data _null_ ;

Infile model1 ;

Input ;

File values ;

If _N_ = 2 then delete ;

Put _infile_ ;

Run ;

Data _null_ ;

Infile model1 ;

Input ;

File labels ;

If _N_ > 2 then delete ;

Put _infile_ ;

Run ;

/*Now import model1values.txt and model1labels.txt with proc import using the tab method instead of xls */

/*You'll have to figure the code!*/

Proc transpose data = model1labels

out = labels

;

var _character_ ;

Run ;

Data labels ;

Set labels (keep = _name_ col1) ;

keep dummy . ; /* null value */

rename col1 = _label_ ;

Run ;

Proc transpose data = labels

out = model1 ;

Run ;

/* Now just add in the values */

data model1 ;

set model1 (obs = 0)

model1values

;

drop _name_ ;

run ;

/* Done! hopefully */

OK if you get error messages with the code let me know and I'll try and help

Richard

Message was edited by: Richard Carson drop statement added - might need to add _label_ and anything else generated by transpose

Re: Read in Excel data into SAS: variable labels in second row

Mandy1 — Wed, 10 Apr 2013 04:01:29 GMT

Richard,

Thank you for sharing with your codes! It is very helpful. I truly appreiciate it.

Mandy