topic Re: proc import -- 40-char field with only numbers is being converted to numeric in SAS Programming

proc import -- 40-char field with only numbers is being converted to numeric

Anna_dlC — Thu, 21 Apr 2022 17:44:27 GMT

I have a csv file that is being automatically export from an app. The unique record identifier is a field called XREF. In the app, it is a 40-character field but all the characters are digits (i.e. 1111111573111111113288888888882704522560). Proc import thinks it's a numeric field and, because it's so large, it's doing that think where it truncates it to that E format (i.e. 1.11111157311E39). How do I get Excel to keep it as a character field? My program will be going into production so there will be no manual import (i.e through Import Wizard) nor the opportunity to manipulate the csv file before importing.

Re: proc import -- 40-char field with only numbers is being converted to numeric

Kurt_Bremser — Thu, 21 Apr 2022 17:49:33 GMT

Simple solution: do not use PROC IMPORT, but write the data step yourself.

Start with the code that was created by PROC IMPORT (take it from the log), and adapt it to your needs.

Re: proc import -- 40-char field with only numbers is being converted to numeric

Tom — Fri, 22 Apr 2022 02:53:09 GMT

Not sure what EXCEL has to do with this. A CSV file is just a text file.

If you know the field is character why are you letting SAS have to GUESS how to read?

You can read the text file yourself with a data step and you will have complete control over how the variables are defined.

If you do want to use a tool to GUESS how to read the file then use one that knows numbers cannot be that large.

https://github.com/sasutils/macros/blob/master/csv2ds.sas

filename csv temp;
options parmcards=csv;

parmcards;
XREF,V1,V2
1111111573111111113288888888882704522560,AAA,123
;

%csv2ds(csv);

11   +data fromcsv;
12   +  infile CSV dlm=',' dsd truncover firstobs=2 ;
13   +  length XREF $40 V1 $3 V2 8 ;
14   +  input XREF -- V2 ;
15   +run;

Plus it generates readable SAS code instead of the convoluted mess that PROC IMPORT generates.

16    proc import datafile=csv dbms=csv out=import replace;
17    run;

18     /**********************************************************************
19     *   PRODUCT:   SAS
20     *   VERSION:   9.4
21     *   CREATOR:   External File Interface
22     *   DATE:      21APR22
23     *   DESC:      Generated SAS Datastep Code
24     *   TEMPLATE SOURCE:  (None Specified.)
25     ***********************************************************************/
26        data WORK.IMPORT    ;
27        %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
28        infile CSV delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
29           informat XREF best32. ;
30           informat V1 $3. ;
31           informat V2 best32. ;
32           format XREF best12. ;
33           format V1 $3. ;
34           format V2 best12. ;
35        input
36                    XREF
37                    V1  $
38                    V2
39        ;
40        if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
41        run;