sas, proc import question

In PROC IMPORT, what does the option GUESSINGROWS do ?
Thanks

Eric B

Cell formatting. A common problem with either the
import wizard or the PROC IMPORT statements is that if
you haven't been consistent with cell formats in each
column, SAS may get confused regarding the type of
data it is actually trying to read. SAS scans the
first 20 rows of an Excel worksheet to determine data
types for each column. If for any particular column
the first 20 rows contain all numbers (such as 3.3,
4.92, 2.0, 5, 3.1, etc.) or blank cells formatted as
numbers, SAS will assign that variable a numeric
format. If any data other than numeric are read in
that column after row 20, SAS will set the value of
that row and column as missing in the dataset.
However, if within the first 20 rows any non-numeric
data are found (such as the characters A,' ,' or a
period for missing values) SAS will assign that
variable to have a character format, even if the
column is defined as numeric.

The number of rows SAS scans when determining data
types can be modified from its default value of 20.
There is an option called "guessingrows" which can be
modified in the registry settings. For details on how
to use this option, see
http://support.sas.com/techsup/unotes/SN/001/001075.html



HTH
Mona





__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Eric ,

From the docs:

GUESSING ROWS=1 to 3276;
scans data for its data type from row 1 to the row number that is specified.

Note: This number should be greater than the value that is specified for
DATAROW=.




IE. it tells SAS how many rows to read through inorder to determine the data
type of the variable being read into SAS.

so if you specify : GUESSING ROWS=1 to 100 ;
SAS will look through the first 100 rows of data you are importing to
determine if it should make the var character or numeric. The default is to
look at the first row I think and then based upon that make it iether a
number or character. Pretty handy if your data is mixed type in Excel and
your first few cells for that column are numbers and then you have some
character. IF you dont specify this SAS will make the var a numeric and
you will get errors and misisng values when you try to read the character
data from that mixed excel column.

Toby Dunn





From: Eric B < XXXX@XXXXX.COM >
Reply-To: Eric B < XXXX@XXXXX.COM >
To: XXXX@XXXXX.COM
Subject: PROC Import question
Date: Mon, 23 Jan 2006 07:17:05 -0800


In PROC IMPORT, what does the option GUESSINGROWS do ?
Thanks

Eric B

Fascinating! While the documentation definitely says, at one point,
exactly what Toby said, the maximum guessingrows number is also listed (in
the documentation) as 32767.

I would guess that the right-most '7' accidentally got deleted as that
sure seems like a more computer-like limit.

Art
---------


specified.
data
to
and

GUESSINGROWS is available to specify how many rows of
data are to be sampled to determine variable attributes.
(i.e. num or char, formats, etc.)



Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of
Eric B
Sent: Monday, January 23, 2006 7:17 AM
To: XXXX@XXXXX.COM
Subject: PROC Import question


In PROC IMPORT, what does the option GUESSINGROWS do ?
Thanks

Eric B

I am using PROC IMPORT to import several identically structured Excel
files whose data in each column is generally consistent, but sometimes
certain columns import as character and sometimes as numeric. When
these datasets are combined, an error occurs because the one column
has been simultaneously defined as both character and numeric type. In
my specific example, the data is usually all numbers, but sometimes
contains an underscore. When it is all numbers, IMPORT read it in as
numeric, and when it contains an underscore, as character. Here is my
PROC IMPORT code:

PROC IMPORT OUT = WORK.&DATANAME
DATAFILE = "&FILENAME"
DBMS = EXCEL97 REPLACE;
GETNAMES = YES;
MIXED = YES;
RUN;

The macro variables are defined correctly, so the IMPORT process
always runs. I was hoping that I could either (A) predefine a
structure, like an existing reference dataset, that IMPORT could
deposit the contents of the Excel file, or (B) inspect and change the
data type of the columns according to some existing reference dataset
after the IMPORT finishes.

Here is the data step that combines the IMPORTed data step with the
existing data.

DATA WORK.COLLATED_DATA;
SET WORK.COLLATED_DATA
WORK.&DATANAME;
RUN;

When the column type switches, for example on the column ACCOUNTID, I
get an error like:

ERROR: Variable ACCOUNTID has been defined as both character and
numeric.

Any help or suggestions would be great.

If you insist on using PROC IMPORT, you are going to have to check the
type of the vairables before you do the append here. Every time.

You could use PROC APPEND with the FORCE option, but that would
have plenty of downside too. The variables of the 'wrong' type in the
data set being appended could get set to missing.

I would consider using Vince DelGobbo's approach. Get the %XLXP2SAS
macro from the SAS website, and use that to turn each file into an XML
version
of your file, and read that into SAS

HTH,
David
_________________________________________________________________
Need a break? Find your escape route with Live Search Maps.
http://maps.live.com/default.aspx?ss=Restaurants ~Hotels~Amusement%20Park&cp=33.832922~-117.915659&style=r&lvl=13&tilt=-90&dir=0&alt=-1000&scene=1118863&encType=1&FORM=MGAC01

Dear all,

i am trying to import an excel worksheet from windows on to UNIX(SAS
V8)

I am using the following code

PROC IMPORT OUT= a
DATAFILE= "C:\temp.xls"
DBMS=EXCEL REPLACE; RUN;

But it's giving me the following error. I have ERROR: DBMS type EXCEL
not valid for import. I tried using all different DBMS type including
EXCEL97, 2000, 4, 5 EXCELCS but it is still giving me error.

I tried to import it as a CSV file but what its doing is, it's
throwing some garbage "Square sign" at the end of the variable
values.

Can someone tell me a work around to this please?

Regards,

Hi,
You need the SAS ACCESS module to be able to use proc import and proc
export with Excel files. Without SAS ACCESS it only allows to export/
import ".csv" and a few other file types.

Zai


> DBMS=EXCEL REP>AC>; RUN;
>
> But it's giving me the following error. I have ERROR: DBMS >ype EXCEL
> not valid for import. I tried using all different DBMS type>including
> EXCEL97, 2000, 4, 5 EXCELCS but it is still giving>me>error.
>
> I tried to import it as a CSV file but what its doin> is, it's
> throwing some garbage "Square sign" at the end of th> variable >> >alues.
>
> Can someone tell me a work around to th>s >lease?
>
> Regards,

Dear SAS-L:

Can I read a csv file with proc import vs. using infile?

I have data like the following:

425,2008-01-25 00:00:00,1
425,2008-02-15 00:00:00,1
425,2008-03-17 00:00:00,1
425,2008-03-24 00:00:00,1
425,2008-04-11 00:00:00,1
425,2008-05-09 00:00:00,1

proc
import


out=csvin

datafile =
"d:\jake\test.txt"


dbms=csv;


getnames=yes;


guessingrows=16000;


datarow=1;

run;


But having some trouble. I'm not searching the SAS manuals for more
information I must have systax errors.

Jake

Jake

Do you actually have a row with variable names because you specify
getnames=yes and then tell sas to look through 16,000 rows to find the
names but you also say that the data start in row 1. Personally, I would
write an input statement myself for a simple file like this.

Also, a better description of the error messages would help in diagnosis.



Nat Wooding
Environmental Specialist III
Dominion, Environmental Biology
4111 Castlewood Rd
Richmond, VA 23234
Phone:804-271-5313, Fax: 804-271-2977



Jake Bee
< XXXX@XXXXX.COM
> To
Sent by: "SAS(r) XXXX@XXXXX.COM
Discussion" cc
< XXXX@XXXXX.COM
GA.EDU> Subject
proc import question

06/13/2008 08:33
AM


Please respond to
Jake Bee
< XXXX@XXXXX.COM
>






Dear SAS-L:

Can I read a csv file with proc import vs. using infile?

I have data like the following:

425,2008-01-25 00:00:00,1
425,2008-02-15 00:00:00,1
425,2008-03-17 00:00:00,1
425,2008-03-24 00:00:00,1
425,2008-04-11 00:00:00,1
425,2008-05-09 00:00:00,1

proc
import


out=csvin

datafile =
"d:\jake\test.txt"


dbms=csv;


getnames=yes;


guessingrows=16000;


datarow=1;

run;


But having some trouble. I'm not searching the SAS manuals for more
information I must have systax errors.

Jake


-----------------------------------------
CONFIDENTIALITY NOTICE: This electronic message contains
information which may be legally confidential and/or privileged and
does not in any case represent a firm ENERGY COMMODITY bid or offer
relating thereto which binds the sender without an additional
express written confirmation to that effect. The information is
intended solely for the individual or entity named above and access
by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, distribution, or use of the
contents of this information is prohibited and may be unlawful. If
you have received this electronic transmission in error, please
reply immediately to the sender that you have received the message
in error, and delete it. Thank you.

No row with var names. I guess that's a problem. Thanks!

Hi,

I am trying to learn the basics of proc import and am slowly making
progress. I often need to work on somewhat dirty spreadsheets in Excel and
so I learned to use the mixed=yes option in proc import and additionally
have changed the value of 'typeguessrows' in the Windows registry to a
very large value...My question: With the latest dataset I am working with,
there are numerous numeric rows that are correctly getting read into SAS
as numeric, and, mixed rows appropriately being created as character. For
some reason, though, there are other seemingly 'numeric' rows in the Excel
file (without any apparent character values) that are stubbornly being
forced to character when read into SAS. I can't figure out why. Has
anybody noticed this too and is there some other attribute in these Excel
data columns that is forcing SAS to have this effect? Thanks for the
help....


Mark

> From: Mark S.

I just answered a similar Q from a user here.

My recommendation was to save the .xls as .csv
and import .csv which has the guessingrows parameter
which import .xls does not have.

One problem that I saw was that row 2 (row 1 contains column names)
had blank in many columns.

Data entry data sets often contain the sparse matrix problem.

User replied that they found one row with values in every column
and moved that up to row 2.

hth

Ronald J. Fehd, IT Spec
HelpDesk Level 3: SAS

Mark,

I agree with Ron that the problem may well be that the column of concern
has a blank in one of the records.

I usually get around such problems by using the excel libname engine,
rather than proc import, and control what is read by preparing a template
of the data types I want.

For example:

libname xlsIN 'c:\exceldata.xls';

data template;
format a 2.;
format b 2.;
format c 2.;
input a b c;
cards;
1 1 1
;

data want;
set template xlsIN.'sheet1$'n;
if _n_ gt 1 then output;
run ;

HTH,
Art
--------