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

Reply
Occasional Contributor
Posts: 6

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

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















Respected Advisor
Posts: 3,777

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

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.

Super Contributor
Posts: 644

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

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

Occasional Contributor
Posts: 6

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


Thank you for the input!

Super Contributor
Posts: 644

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

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

Occasional Contributor
Posts: 6

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

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

Super Contributor
Posts: 644

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

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

Occasional Contributor
Posts: 6

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

Richard,

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

Mandy

Ask a Question
Discussion stats
  • 7 replies
  • 2365 views
  • 6 likes
  • 3 in conversation