sas >> proc import .csv file question!

by Xuhong » Tue, 11 Mar 2008 01:56:00 GMT

Hello, folks,

I use "proc import " to import the .csv file to SAS. I was lazy to
find the longest width for each categorical variables since the raw
file is too big (encounter data). And SAS maybe only read the first
12? variables to set the default variable length to this categorical
variable and that caused the truncation somehow.

So, I came here to ask if there is a good method to read large .CSV
files without truncation problems.

I appreciate your help very much!


sas >> proc import .csv file question!

by art297 » Tue, 11 Mar 2008 02:46:05 GMT


Take a look at Paul Choate's instructions for changing the guessingrows
setting: http://xrl.us/bhgof

HTH,
Art
--------





sas >> proc import .csv file question!

by rjf2 » Tue, 11 Mar 2008 03:47:19 GMT

> From: Xuhong

guessingrows is the statement you want to add to your proc import

%Let FileNameCsv = SAS-Rpt-271-2008-03-01;

%Let Out_Data = Library.%sysfunc(translate(&FileNameCsv,__,-+));

PROC Import datafile = "..\csv\&FileNameCsv..csv"
out = &Out_Data.
dbms = csv %*see identifier list;
replace %*overwrite out= dataset;
; *closure: proc;
guessingrows=32767;

PROC SQL;describe table &Out_Data.;
;quit;

Ron Fehd the macro maven CDC Atlanta GA USA RJF2 at cdc dot gov


Similar Threads

1. proc import question for CSV file

2. proc import, csv file with comma in varaibles

Does anyone know how to imprt a csv file, which inlude comma as
variable values (such as firstname.lastname)  to a sas file?

I try to use wizard to do this, but ger error messages. I don't think
dsd or dlm will take of this because from the wizard log, they use dsd
and dlm options.

Thanks.

Min

3. GUESSINGCOLUMNS: PROC IMPORT/IMPORT Wizard CSV/TAB/DLM

4. Problem with importing from a csv file

Hello again

I have a .csv file with two columns, named CORE and CLUSTER. Core is a character field, cluster is numeric.  The first few lines of CORE are 6 characters long e.g.

00614D
08185C
08394B
08545A

but later on, they are longer, e.g.,
BW27011a
BW28011a
BW29011a
BW31011a
EN20101A
EN20102A


(Yes, I know, that's a terrible way to write a variable, but it was due to it being input over a period of 25 years or so, from machines that required certain formats, which differed over time....)

I am importing it into SAS using

PROC IMPORT OUT= WORK.twoclust
            DATAFILE= "C:\Fivelead\Data\twoclust.csv"
            DBMS=CSV REPLACE;
     GETNAMES=YES;
     DATAROW=2;
RUN;


and this is creating two variable, appropriately CORE and CLUSTER, but CORE is formatted as $6.  This causes major problems, because the first 6 characters of CORE are not unique.

Two questions:
1) How can I fix this?
2) Isn't this a horrible bug?

Thanks in advance

Peter

PS I will also write to SAS tech support about this.


Peter L. Flom, PhD
Statistical Consultant
www DOT peterflom DOT com

5. Import CSV file, variable field numbers

6. Importing CSV file into SAS (limiting # of records )

Hi all,
I am importing a large csv file(2 Gb) into sas using the infile
statement. Is there any way to read/write only a small number of
records (for e.g. 100) into SAS so that I can check my dataset and make
sure all my formats have been correctly laid out and I am reading in
the file correctly.
Thanks in advance

7. Importing large CSV files

8. any rule of importing .csv file to SAS data

Hey, guys,

Is there any rule of importing .csv file to SAS data?
I have a very large excel csv file, after using wizard to import file,
the information from log said the data set is successfully created.
But when I checked detail of variables,  I found some variable were
not assigned enough length, for example one character variabel in
excel csv file is 4 digits , but in sas file the length of the
variable is only 1, so it is truncated when SAS did the data import.
But no warning in the log.

To test how SAS determin the length of a varaible when it imports csv
file, I create a fake excel csv file as below
firstname	lastname	prod_typ
nacy	John
nacy1	john2	PBSC

there is no trailing blank after each value,
But after I run the import wizard, I got the information as
*********************************************************************/
9        data WORK.try                                    ;
10      %let _EFIERR_ = 0; /* set the ERROR detection macro variable
*/
11       infile 'C:\Documents and Settings\esktop\test.csv' delimiter
= ',' MISSOVER DSD
11 ! lrecl=32767 firstobs=2 ;
12          informat firstname $6. ;
13          informat lastname $6. ;
14          informat prod_typ $5. ;
15          format firstname $6. ;
16          format lastname $6. ;
17          format prod_typ $5. ;
18       input
19                   firstname $
20                   lastname $
21                   prod_typ $
22       ;
23       if _ERROR_ then call symput('_EFIERR_',1);  /* set ERROR
detection macro variable */
24       run;

NOTE: Numeric values have been converted to character values at the
places given by:
      (Line):(Column).
      23:44
NOTE: The infile 'C:\Documents and Settings\Desktop\test.csv' is:
      File Name=C:\Documents and Settings\Desktop\test.csv,
      RECFM=V,LRECL=32767

NOTE: 2 records were read from the infile 'C:\Documents and Settings
\Desktop\test.csv'.
      The minimum record length was 11.
      The maximum record length was 16.
NOTE: The data set WORK.TRY has 2 observations and 3 variables.
NOTE: DATA statement used:
      real time           0.02 seconds
      cpu time            0.02 seconds


2 rows created in WORK.try                                  from C:
\Documents and
Settings\Desktop\test.csv.



NOTE: WORK.TRY was successfully created.

Note the variable is not as we expected, it seems SAS automatically
add one more digit for each variable.
Is any sas document to help understand the procudeure of importing
data ? Thanks.

Mindy