Importing a csv file using infile statement

[Vimal08]

I am using an Infile statement to import data. So when I am placing the csv file from windows to my server at that time it is running fine and all records are populating in my dataset, but when I am generating the same csv file on server through a python code and then running my Infile statement, the dataset is getting generated with no records. Please help me to resolve this issue.

[ballardw]

Any time you have unexpected behavior it is best practice to look at the log.

If you don't understand what the log maybe telling you then copy the text from the log and in your question here paste that text into a text box opened using the </> icon above the message window.

Important: do not just supply error messages, the code for an entire procedure or data step from the log with all of the notes, warnings and/or errors is important.

Depending on just how you are using Python you may need to look for diagnostics other than in SAS if your problem actually is in the Python code.

[Vimal08]

1    %studio_hide_wrapper;
82   libname tbl '/home/sas/Demo/Dashboard/Assets/Dashboard_Base/Tables/Extracted_Files';
NOTE: Libref TBL was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /home/sas/Demo/Dashboard/Assets/Dashboard_Base/Tables/Extracted_Files
83   
84   %let Path = /home/sas/Demo/Dashboard/Assets/Dashboard_Base/Tables/Extracted_Files;
85   
86   DATA WORK.SAS_DISC_LOG;
87       LENGTH
88           FILE_ID          $ 15
89           FILE_PTH         $ 150
90           FILE_NM          $ 59
91           FILE_USR_NM      $ 13
92           FILE_SAS_F_ID    $ 15
93           FILE_SAS_F_LOC   $ 128
94           FILE_SAS_F_NM    $ 35
95           FILE_SAS_STP     $ 4
96           FILE_SAS_STP_NM  $ 8
97           FILE_LN_NUM        8
98           FILE_SAS_INP_LIB $ 8
99           FILE_SAS_INP_TBL $ 32
100          FILE_SAS_INP_FIL_NM $ 76
101          FILE_SAS_INP_ROW_RD $ 9
102          FILE_SAS_OUT_LIB $ 15
103          FILE_SAS_OUT_TBL $ 56
104          FILE_SAS_ROW_WRT $ 15
105          FILE_SAS_INP_MUL_FLG $ 2
106          FILE_SAS_INP_MUL_TBLS $ 3
107          FILE_EXC_DT        8
108          FILE_SAS_EXC_TM    8
109          FILE_SAS_EXC_CPU_TM   8
110          FILE_SAS_EXC_RL_TM   8
111          FILE_SAS_PROC_CAT $ 32
112          FILE_SAS_PROC_PROD $ 50
113          FILE_SAS_MIGR_DISP $ 16
114          FILE_SAS_MIGR_RUL_ID $ 13
115          FILE_SAS_MIGR_REC_ACT $ 604
116          FILE_SAS_PROC_INMEM_FLG   8
117          FILE_SAS_PROC_ELT_FLG   8
118          FILE_SAS_PROC_GRID_FLG   8
119          FILE_SAS_PROC_INDB_FLG   8
120          FILE_SAS_SRC_TYP $ 3 ;
121      FORMAT
122          FILE_ID          $CHAR15.
123          FILE_PTH         $CHAR150.
124          FILE_NM          $CHAR59.
125          FILE_USR_NM      $CHAR13.
126          FILE_SAS_F_ID    $CHAR15.
127          FILE_SAS_F_LOC   $CHAR128.
128          FILE_SAS_F_NM    $CHAR35.
129          FILE_SAS_STP     $CHAR4.
130          FILE_SAS_STP_NM  $CHAR8.
131          FILE_LN_NUM      BEST4.
132          FILE_SAS_INP_LIB $CHAR8.
133          FILE_SAS_INP_TBL $CHAR32.
134          FILE_SAS_INP_FIL_NM $CHAR76.
135          FILE_SAS_INP_ROW_RD $CHAR9.
136          FILE_SAS_OUT_LIB $CHAR15.
137          FILE_SAS_OUT_TBL $CHAR56.
138          FILE_SAS_ROW_WRT $CHAR15.
139          FILE_SAS_INP_MUL_FLG $CHAR2.
140          FILE_SAS_INP_MUL_TBLS $CHAR3.
141          FILE_EXC_DT      YYMMDD10.
142          FILE_SAS_EXC_TM  time11.
143          FILE_SAS_EXC_CPU_TM BEST9.
144          FILE_SAS_EXC_RL_TM BEST8.
145          FILE_SAS_PROC_CAT $CHAR32.
146          FILE_SAS_PROC_PROD $CHAR50.
147          FILE_SAS_MIGR_DISP $CHAR16.
148          FILE_SAS_MIGR_RUL_ID $CHAR13.
149          FILE_SAS_MIGR_REC_ACT $CHAR604.
150          FILE_SAS_PROC_INMEM_FLG BEST1.
151          FILE_SAS_PROC_ELT_FLG BEST1.
152          FILE_SAS_PROC_GRID_FLG BEST1.
153          FILE_SAS_PROC_INDB_FLG BEST1.
154          FILE_SAS_SRC_TYP $CHAR3. ;
155      INFORMAT
156          FILE_ID          $CHAR15.
157          FILE_PTH         $CHAR150.
158          FILE_NM          $CHAR59.
159          FILE_USR_NM      $CHAR13.
160          FILE_SAS_F_ID    $CHAR15.
161          FILE_SAS_F_LOC   $CHAR128.
162          FILE_SAS_F_NM    $CHAR35.
163          FILE_SAS_STP     $CHAR4.
164          FILE_SAS_STP_NM  $CHAR8.
165          FILE_LN_NUM      BEST4.
166          FILE_SAS_INP_LIB $CHAR8.
167          FILE_SAS_INP_TBL $CHAR32.
168          FILE_SAS_INP_FIL_NM $CHAR76.
169          FILE_SAS_INP_ROW_RD $CHAR9.
170          FILE_SAS_OUT_LIB $CHAR15.
171          FILE_SAS_OUT_TBL $CHAR56.
172          FILE_SAS_ROW_WRT $CHAR15.
173          FILE_SAS_INP_MUL_FLG $CHAR2.
174          FILE_SAS_INP_MUL_TBLS $CHAR3.
175          FILE_EXC_DT      YYMMDD10.
176          FILE_SAS_EXC_TM  time11.
177          FILE_SAS_EXC_CPU_TM BEST9.
178          FILE_SAS_EXC_RL_TM BEST8.
179          FILE_SAS_PROC_CAT $CHAR32.
180          FILE_SAS_PROC_PROD $CHAR50.
181          FILE_SAS_MIGR_DISP $CHAR16.
182          FILE_SAS_MIGR_RUL_ID $CHAR13.
183          FILE_SAS_MIGR_REC_ACT $CHAR604.
184          FILE_SAS_PROC_INMEM_FLG BEST1.
185          FILE_SAS_PROC_ELT_FLG BEST1.
186          FILE_SAS_PROC_GRID_FLG BEST1.
187          FILE_SAS_PROC_INDB_FLG BEST1.
188          FILE_SAS_SRC_TYP $CHAR3. ;
189  INFILE "&Path./SAS_DISC_LOG_O.csv"
190          LRECL=1212
191          ENCODING="WLATIN1"
192          TERMSTR=CRLF
193          DLM=','
194          MISSOVER FIRSTOBS=2
195          DSD ;
196      INPUT
197          FILE_ID          : $CHAR15.
198          FILE_PTH         : $CHAR150.
199          FILE_NM          : $CHAR59.
200          FILE_USR_NM      : $CHAR13.
201          FILE_SAS_F_ID    : $CHAR15.
202          FILE_SAS_F_LOC   : $CHAR128.
203          FILE_SAS_F_NM    : $CHAR35.
204          FILE_SAS_STP     : $CHAR4.
205          FILE_SAS_STP_NM  : $CHAR8.
206          FILE_LN_NUM      : ?? BEST4.
207          FILE_SAS_INP_LIB : $CHAR8.
208          FILE_SAS_INP_TBL : $CHAR32.
209          FILE_SAS_INP_FIL_NM : $CHAR76.
210          FILE_SAS_INP_ROW_RD : $CHAR9.
211          FILE_SAS_OUT_LIB : $CHAR15.
212          FILE_SAS_OUT_TBL : $CHAR56.
213          FILE_SAS_ROW_WRT : $CHAR15.
214          FILE_SAS_INP_MUL_FLG : $CHAR2.
215          FILE_SAS_INP_MUL_TBLS : $CHAR3.
216          FILE_EXC_DT      : ?? YYMMDD10.
217          FILE_SAS_EXC_TM  : ?? time11.
218          FILE_SAS_EXC_CPU_TM : ?? COMMA9.
219          FILE_SAS_EXC_RL_TM : ?? COMMA8.
220          FILE_SAS_PROC_CAT : $CHAR32.
221          FILE_SAS_PROC_PROD : $CHAR50.
222          FILE_SAS_MIGR_DISP : $CHAR16.
223          FILE_SAS_MIGR_RUL_ID : $CHAR13.
224          FILE_SAS_MIGR_REC_ACT : $CHAR604.
225          FILE_SAS_PROC_INMEM_FLG : ?? BEST1.
226          FILE_SAS_PROC_ELT_FLG : ?? BEST1.
227          FILE_SAS_PROC_GRID_FLG : ?? BEST1.
228          FILE_SAS_PROC_INDB_FLG : ?? BEST1.
229          FILE_SAS_SRC_TYP : $CHAR3. ;
230  RUN;
NOTE: The infile "/home/sas/Demo/Dashboard/Assets/Dashboard_Base/Tables/Extracted_Files/SAS_DISC_LOG_O.csv" is:
      Filename=/home/sas/Demo/Dashboard/Assets/Dashboard_Base/Tables/Extracted_Files/SAS_DISC_LOG_O.csv,
      Owner Name=sas,Group Name=sas,
      Access Permission=-rw-rw-r--,
      Last Modified=02Sep2021:13:19:13,
      File Size (bytes)=208805
NOTE: 0 records were read from the infile 
      "/home/sas/Demo/Dashboard/Assets/Dashboard_Base/Tables/Extracted_Files/SAS_DISC_LOG_O.csv".
NOTE: The data set WORK.SAS_DISC_LOG has 0 observations and 33 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
231  
232  
233  data sas_disc_log1 (drop=file_sas_inp_row_rd file_sas_row_wrt
234  rename=(file_sas_inp_row_rd1=file_sas_inp_row_rd file_sas_row_wrt1=file_sas_row_wrt) );
235  set sas_disc_log  ;
236  file_sas_inp_row_rd1=compress(file_sas_inp_row_rd,':');
237  file_sas_row_wrt1=compress(file_sas_row_wrt,':');
238  run;
NOTE: There were 0 observations read from the data set WORK.SAS_DISC_LOG.
NOTE: The data set WORK.SAS_DISC_LOG1 has 0 observations and 33 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.02 seconds

I feel there is nothing wrong in this code and also code is executed without any error or warning. Maybe this is a csv compatibility issue, but I am not sure. 

[Tom · Accepted solution]

The issue is you told the INFILE statement to skip the first line. It found zero lines to read, but the file is not zero bytes long.  So the whole file is in the first line.

Did you create this file using a MAC version of EXCEL?  Apparently EXCEL never learned that MacOS is now a flavor of UNIX so it is still writing text files with just CR as the end of line marker as if Mac was still using the same operating system in used in the 1980s.

Try adding TERMSTR=CR to your INFILE statement.

NOTE:

• You don't need have an INFORMAT statement and also specify informats in the INPUT statement. 
• You do not need to attach formats or informats to most character or numeric variables. 
  • The only exceptions I see in your program are these two variables that contain date and time values.

FILE_EXC_DT      YYMMDD10.
FILE_SAS_EXC_TM  time11.

[Vimal08]

Hi, I have updated TERMSTR=LF and it worked.

Thank you for your help.