BookmarkSubscribeRSS Feed
Mandy1
Fluorite | Level 6

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















7 REPLIES 7
data_null__
Jade | Level 19

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.

RichardinOz
Quartz | Level 8

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

Mandy1
Fluorite | Level 6


Thank you for the input!

RichardinOz
Quartz | Level 8

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

Mandy1
Fluorite | Level 6

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

RichardinOz
Quartz | Level 8

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

Mandy1
Fluorite | Level 6

Richard,

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

Mandy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5298 views
  • 6 likes
  • 3 in conversation