I'm reading in a data set that has large UIDs in one column.
When I try to import it, I get a 'Floating Point Overflow' Error (Full Log file at the end).
I believe its because of a column named 'uid' that has values in it like '68MAPO710002.754153181411', '508751309549181412', '566315-137495181411', and '20MAPO71000007.2903467181411' for example.
How can I tell proc import to read in column 'UID' as a character column and not as a number?
1
76 PROC IMPORT DATAFILE=BILFILE
77 DBMS=XLSX replace
78 OUT=bill1;
79 GETNAMES=YES;
80 RUN;
ERROR: Floating Point Overflow.
ERROR: Termination due to Floating Point Exception
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
81
82 PROC IMPORT DATAFILE=STJOFILE
83 DBMS=CSV replace
84 OUT=stjoseph1;
85 GETNAMES=YES;
86 RUN;
Add GUESSINGROWS=MAX to your PROC IMPORT code will likely fix it.
Fastest solution for run time:
Copy the code from the log (the IMPORT code that specifies the types, formats/informats), remove the line numbers, modify the types to be what you need them to be and run that code.
The main analytical programming languages (R/Python/SAS) all have guessing import procedures or options to specify your types. When you use a guessing procedure it will sometimes guess wrong. The majority of production systems do not recommend using a guessing procedure to import data regularly. For homework or basic proof of concept it's fine. But if your software version changes, the file changes slightly, you can get different results. So long term, the second option is more stable.
I'm reading in a data set that has large UIDs in one column.
When I try to import it, I get a 'Floating Point Overflow' Error (Full Log file at the end).
I believe its because of a column named 'uid' that has values in it like '68MAPO710002.754153181411', '508751309549181412', '566315-137495181411', and '20MAPO71000007.2903467181411' for example.
How can I tell proc import to read in column 'UID' as a character column and not as a number?
1 OPTIONS NOSYNTAXCHECK;
2 TITLE;
3 FOOTNOTE;
4 OPTIONS LOCALE=en_US DFLANG=LOCALE;
5 DATA _NULL_;
6 RUN;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
7 OPTIONS VALIDVARNAME=ANY;
8 OPTIONS VALIDMEMNAME=EXTEND;
9 FILENAME _HTMLOUT TEMP;
10 FILENAME _RTFOUT TEMP ENCODING='UTF-8';
11 FILENAME _PDFOUT TEMP;
12 FILENAME _GSFNAME TEMP;
13 FILENAME _DATAOUT TEMP;
14 %LET SYSCC=0;
15 %LET _CLIENTAPP='SAS Studio';
16 %LET _CLIENTAPPABREV=Studio;
17 %LET _CLIENTAPPVERSION=3.81;
18 %LET _CLIENTVERSION=3.81;
19 %LET _CLIENTMODE=wip;
20 %LET _SASSERVERNAME=%BQUOTE(SASApp);
21 %LET _SASHOSTNAME=%BQUOTE(HTHAnalytics);
22 %LET _SASPROGRAMFILEHOST=%BQUOTE(HTHAnalytics);
23 %LET _CLIENTUSERID=%BQUOTE(sasuser);
24 %LET _CLIENTUSERNAME=%BQUOTE(sasuser);
25 %LET CLIENTMACHINE=%BQUOTE(172.20.20.40);
26 %LET _CLIENTMACHINE=%BQUOTE(172.20.20.40);
27 %let SASWORKLOCATION="%sysfunc(getoption(work))/";
28 FILENAME _CWD '.';
29 DATA _NULL_;
30 CALL SYMPUT('_SASWORKINGDIR',PATHNAME('_CWD'));
31 RUN;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
32 FILENAME _CWD;
NOTE: Fileref _CWD has been deassigned.
33
34 %LET _SASPROGRAMFILE = %NRQUOTE(%NRSTR(C:/Users/sasuser/Documents/My SAS Files/Census Tract Analysis %(Dec 2021%).sas));
35 %LET _BASEURL = %BQUOTE(https://hthanalytics.plexis.org/SASStudio/);
36 %LET _EXECENV=SASStudio;
37 DATA _NULL_;
38 CALL SYMPUT("GRAPHINIT","");
39 CALL SYMPUT("GRAPHTERM","");
40 RC=TSLVL('SASXGOPT','N');
41 _ERROR_=0;
42 IF (RC^=' ') THEN DO;
43 CALL SYMPUT("GRAPHINIT","GOPTIONS RESET=ALL GSFNAME=_GSFNAME;");
44 CALL SYMPUT("GRAPHTERM","GOPTIONS NOACCESSIBLE;");
45 END;
46 RUN;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
47 DATA _NULL_;
48 RC=SYSPROD("PRODNUM002");
49 IF (RC^=1) THEN DO;
50 CALL SYMPUT("GRAPHINIT","");
51 CALL SYMPUT("GRAPHTERM","");
52 END;
53 RUN;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
54 %LET _DATAOUT_MIME_TYPE=;
55 %LET _DATAOUT_NAME=;
56 %LET _DATAOUT_TABLE=;
57 %LET _DATAOUT_URL=;
58 %SYMDEL _DATAOUT_MIME_TYPE _DATAOUT_NAME _DATAOUT_URL _DATAOUT_TABLE;
59 %LET _SASWS_ = %BQUOTE(C:/Users/sasuser);
60 %LET _SASWSTEMP_=%BQUOTE(C:/Users/sasuser/AppData/Roaming/SAS/SASStudio/.images/cb9886ad-62d2-4453-817d-a8d87d472ae5);
61 ODS LISTING CLOSE;
62 ODS AUTONAVIGATE OFF;
63 ODS GRAPHICS ON;
64 ODS HTML5 (ID=WEB) DEVICE=PNG GPATH="&_SASWSTEMP_" ENCODING=utf8 FILE=_HTMLOUT (TITLE='Results: Program 1')
64 ! STYLE=Htmlblue OPTIONS(BITMAP_MODE='INLINE' OUTLINE='ON' SVG_MODE='INLINE'
64 ! CSS_PREFIX='.ods_cb9886ad-62d2-4453-817d-a8d87d472ae5' BODY_ID='div_cb9886ad-62d2-4453-817d-a8d87d472ae5' );
NOTE: Writing HTML5(WEB) Body file: _HTMLOUT
65 ODS RTF (ID=WEB) STYLE=Rtf FILE=_RTFOUT sasdate;
NOTE: Writing RTF Body file: _RTFOUT
66 ODS PDF (ID=WEB) STYLE=Pearl FILE=_PDFOUT;
NOTE: Writing ODS PDF(WEB) output to DISK destination "_PDFOUT", printer "PDF".
67 &GRAPHINIT;
68 OPTIONS FIRSTOBS=1;
69 OPTIONS OBS=MAX;
70 OPTIONS DTRESET DATE NUMBER NOTES;
71 OPTIONS NOSYNTAXCHECK;
72
73 FILENAME BILFILE "E:/SJC SAS Data/Census/bill1.xlsx";
74 FILENAME STJOFILE "E:/SJC SAS Data/Census/ST._JOSEPH_4316_10_8_2021.csv";
75
76 PROC IMPORT DATAFILE=BILFILE
77 DBMS=XLSX replace
78 OUT=bill1;
79 GETNAMES=YES;
80 RUN;
ERROR: Floating Point Overflow.
ERROR: Termination due to Floating Point Exception
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
81
82 PROC IMPORT DATAFILE=STJOFILE
83 DBMS=CSV replace
84 OUT=stjoseph1;
85 GETNAMES=YES;
86 RUN;
NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
87 /**********************************************************************
88 * PRODUCT: SAS
89 * VERSION: 9.4
90 * CREATOR: External File Interface
91 * DATE: 30NOV21
92 * DESC: Generated SAS Datastep Code
93 * TEMPLATE SOURCE: (None Specified.)
94 ***********************************************************************/
95 data WORK.STJOSEPH1 ;
96 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
97 infile STJOFILE delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
98 informat facility_description $23. ;
99 informat irms_name $40. ;
100 informat facility_name $32. ;
101 informat facility_address_street1 $28. ;
102 informat facility_address_city $12. ;
103 informat facility_address_zip best32. ;
104 informat facility_county $10. ;
105 informat facility_address_state $2. ;
106 informat facility_county_fips best32. ;
107 informat asiis_pat_id_ptr best32. ;
108 informat pat_first_name $11. ;
109 informat pat_middle_name $1. ;
110 informat pat_last_name $17. ;
111 informat pat_address_street1 $32. ;
112 informat pat_address_city $10. ;
113 informat pat_address_zip best32. ;
114 informat pat_county $10. ;
115 informat pat_address_state $2. ;
116 informat pat_birth_date mmddyy10. ;
117 informat pat_county_fips best32. ;
118 informat pat_race best32. ;
119 informat pat_ethnicity_code best32. ;
120 informat pat_gender $1. ;
121 informat cdc_vacc_code best32. ;
122 informat manu_code $3. ;
123 informat manu_description $17. ;
124 informat lot_num $7. ;
125 informat expiration_date mmddyy10. ;
126 informat vacc_date mmddyy10. ;
127 informat vacc_description $50. ;
128 informat anatomical_site $8. ;
129 informat dose_number best32. ;
130 informat series_complete $1. ;
131 informat last_irms_pat_id_to_update $20. ;
132 informat last_irms_sys_id_to_update best32. ;
133 informat insert_stamp mmddyy10. ;
134 informat update_stamp mmddyy10. ;
135 informat deletion_date $1. ;
136 informat historical_rptd $1. ;
137 informat facility_lhd_jurisdiction $10. ;
138 informat pat_lhd_jurisdiction $10. ;
139 format facility_description $23. ;
140 format irms_name $40. ;
141 format facility_name $32. ;
142 format facility_address_street1 $28. ;
143 format facility_address_city $12. ;
144 format facility_address_zip best12. ;
145 format facility_county $10. ;
146 format facility_address_state $2. ;
147 format facility_county_fips best12. ;
148 format asiis_pat_id_ptr best12. ;
149 format pat_first_name $11. ;
150 format pat_middle_name $1. ;
151 format pat_last_name $17. ;
152 format pat_address_street1 $32. ;
153 format pat_address_city $10. ;
154 format pat_address_zip best12. ;
155 format pat_county $10. ;
156 format pat_address_state $2. ;
157 format pat_birth_date mmddyy10. ;
158 format pat_county_fips best12. ;
159 format pat_race best12. ;
160 format pat_ethnicity_code best12. ;
161 format pat_gender $1. ;
162 format cdc_vacc_code best12. ;
163 format manu_code $3. ;
164 format manu_description $17. ;
165 format lot_num $7. ;
166 format expiration_date mmddyy10. ;
167 format vacc_date mmddyy10. ;
168 format vacc_description $50. ;
169 format anatomical_site $8. ;
170 format dose_number best12. ;
171 format series_complete $1. ;
172 format last_irms_pat_id_to_update $20. ;
173 format last_irms_sys_id_to_update best12. ;
174 format insert_stamp mmddyy10. ;
175 format update_stamp mmddyy10. ;
176 format deletion_date $1. ;
177 format historical_rptd $1. ;
178 format facility_lhd_jurisdiction $10. ;
179 format pat_lhd_jurisdiction $10. ;
180 input
181 facility_description $
182 irms_name $
183 facility_name $
184 facility_address_street1 $
185 facility_address_city $
186 facility_address_zip
187 facility_county $
188 facility_address_state $
189 facility_county_fips
190 asiis_pat_id_ptr
191 pat_first_name $
192 pat_middle_name $
193 pat_last_name $
194 pat_address_street1 $
195 pat_address_city $
196 pat_address_zip
197 pat_county $
198 pat_address_state $
199 pat_birth_date
200 pat_county_fips
201 pat_race
202 pat_ethnicity_code
203 pat_gender $
204 cdc_vacc_code
205 manu_code $
206 manu_description $
207 lot_num $
208 expiration_date
209 vacc_date
210 vacc_description $
211 anatomical_site $
212 dose_number
213 series_complete $
214 last_irms_pat_id_to_update $
215 last_irms_sys_id_to_update
216 insert_stamp
217 update_stamp
218 deletion_date $
219 historical_rptd $
220 facility_lhd_jurisdiction $
221 pat_lhd_jurisdiction $
222 ;
223 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
224 run;
NOTE: The infile STJOFILE is:
Filename=E:\SJC SAS Data\Census\ST._JOSEPH_4316_10_8_2021.csv,
RECFM=V,LRECL=32767,
File Size (bytes)=120232091,
Last Modified=29Nov2021:06:37:50,
Create Time=30Nov2021:12:00:32
NOTE: 313865 records were read from the infile STJOFILE.
The minimum record length was 246.
The maximum record length was 443.
NOTE: The data set WORK.STJOSEPH1 has 313865 observations and 41 variables.
NOTE: DATA statement used (Total process time):
real time 1.48 seconds
cpu time 1.48 seconds
313865 rows created in WORK.STJOSEPH1 from STJOFILE.
NOTE: WORK.STJOSEPH1 data set was successfully created.
NOTE: The data set WORK.STJOSEPH1 has 313865 observations and 41 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1.58 seconds
cpu time 1.59 seconds
225
226 /* WE ASSUME BOTH THE FILES ARE IN THE RIGHT ORDER */
227 /* WE CREATE A UID COLUMN FOR (1,2,3,..N) FOR BOTHT THE DATA SETS */
228
229 data bill1;
230 set bill1;
ERROR: File WORK.BILL1.DATA does not exist.
231 IDNew=_n_;
232 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.BILL1 may be incomplete. When this step was stopped there were 0 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
233
234 data stjoseph1;
235 set stjoseph1;
236 IDNew=_n_;
237 run;
NOTE: There were 313865 observations read from the data set WORK.STJOSEPH1.
NOTE: The data set WORK.STJOSEPH1 has 313865 observations and 42 variables.
NOTE: DATA statement used (Total process time):
real time 0.21 seconds
cpu time 0.21 seconds
238
239 OPTIONS NOSYNTAXCHECK;
240 ODS HTML CLOSE;
241 &GRAPHTERM; ;*';*";*/;RUN;QUIT;
242 QUIT;RUN;
243 ODS HTML5 (ID=WEB) CLOSE;
244
245 ODS RTF (ID=WEB) CLOSE;
246 ODS PDF (ID=WEB) CLOSE;
NOTE: ODS PDF(WEB) printed no output.
(This sometimes results from failing to place a RUN statement before the ODS PDF(WEB) CLOSE statement.)
247 FILENAME _GSFNAME;
NOTE: Fileref _GSFNAME has been deassigned.
248 DATA _NULL_;
249 RUN;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
250 OPTIONS NOTES STIMER SOURCE SYNTAXCHECK;
251
