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.
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: