Solved

Import excel in SAS

Posted on 2014-01-30
2
843 Views
Last Modified: 2016-02-10
Hi,

I have a SAS dataset with 73 fields.
I import with the following code an xlsx file with the same fieldnames as the SAS dataset:
options mprint symbolgen mlogic ;
libname xls pcfiles path='c:\data\cyfcorr.xlsx';
libname xxx 'c:\data\';
data cyf_tb;
    set xxx.cyf;
run;
data cyf_corr;
    set xls.'Sheet1$'n;
run;
libname xls clear;

data test;
   update bcact start decri etc....;
   by key;
run;
I get the following errors, 72 times.
ERROR: Variable bcact has been defined as both character and numeric.
ERROR: Variable start has been defined as both character and numeric.
ERROR: Variable decri has been defined as both character and numeric.
etc......

How can i solve this!

Tx
0
Comment
Question by:sonmic
  • 2
2 Comments
 
LVL 8

Expert Comment

by:ShannonEE
ID: 39822626
Hi there sonmic,

If you want to go the way you have set out above then

change

libname xls pcfiles path='c:\data\cyfcorr.xlsx';

Open in new window

to
libname xls pcfiles path='c:\data\cyfcorr.xlsx'
                     DBSASTYPE=(bcact = NUMERIC  start=NUMERIC
                     decri=NUMERIC);

Open in new window


That is assuming that your current SAS dataset    xxx.cyf   has numeric data  in these 3 columns.  The DBSASTYPE tells sas when importingb the data to use the type specified instead of trying to guess the type.

However it is possible (because of the contents of the spreadsheet) that the data wont convert very well.  You will also need to review the spreadsheet in excel and check if the column is truly numeric all the way down.

-----

My guess is that you need the columns to be numeric.  It might be that the sas dataset    xxx.cyf    has these columns as character.  In that case the libname statement should have the DBSASTYPE set to CHAR.

Read all about it in

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#p0w9ox0g4b3yphn1deuim9yvu092a.htm


 Ian
0
 
LVL 8

Accepted Solution

by:
ShannonEE earned 500 total points
ID: 39822648
Hi there sonmic,  (again)

There are many other ways of importing spreadsheet data into SAS.

If you have enterprise guide then you will have better control in the importing if you add the excel spreadsheet to your current project.

Another way -  you can go into excel and export the data as either tab or comma separated text files.  Then importing into SAS is easy and you have the benefit of being able to use an editor to check the data before importing.

Ian
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VMWare environment audit 8 105
mysql database, schema and table creation 13 79
Shrink multiple databases at once 4 52
VB.Net SQL Query 2 Tables Different Databases 3 43
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question