SAS Script Times Out When Importing CSV - Need Help

[ccarswell]

%let daminput_THREE_PART_OFFER_DLY = /mnt/share/nPROD_PVTdata/DAM/DAM_OD_07-20-2023-121124/THREE_PART_OFFER_DLY.csv;
%let daminput_THREE_PART_OFFER_INT = /mnt/share/nPROD_PVTdata/DAM/DAM_OD_07-20-2023-121124/THREE_PART_OFFER_INT.csv;


/* 4. Import the first CSV file */
data THREE_PART_OFFER_DLY;
    infile "&daminput_THREE_PART_OFFER_DLY" delimiter = ',' lrecl=32767 firstobs=2;
    informat BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID best32. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12. RMR_FLAG $1.;
    format BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID 22. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12. RMR_FLAG $1.;
    input BID_TYPE COMBINED_CYCLE_NAME ID PARTICIPANT_NAME RESOURCE_NAME RESOURCE_TYPE RMR_FLAG;
run;

[Reeza]

How big is the file?

[ccarswell]

small - 1.6 MB

[Reeza]

Are you getting an error? Otherwise, that seems like a network issue that you may need to check with your IT team as to why it can't connect with the file?

You could try copying the file over to the work library temporarily before reading but really it shouldn't matter.

[mkeintz]

Your problem is not the size of your CSV file.

But a clue may be in the fact that the file location is under the main directory /mnt.  This commonly refers to an offline storage medium that has to be physically mounted onto a device connected to the computer system.  Think of it a little like a floppy disk, or cd-rom on a desktop computer.  They have to be  "mounted" on a connected floppy drive or cd drive.  Back in the day, we would have to wait for the system operator to fetch a disk (think of a fixed stack of large pizzas) and attach it to whatever drive the operating system indicated.

So it is possible you are waiting for (1) the operating system to detect an available disk drive, or (2) the operator to "mount" the needed storage medium.

I think there are others on this forum with much more direct experience who can say whether my conjecture is possible.

[ccarswell]

Thank you for the insight about the /mnt directory. I've confirmed with IT that the mount is active and available. Still puzzled why an accessible file would cause a timeout—open to any further ideas.

[Reeza]

What happens when you assign a fileref to that file?

[ccarswell]

It doesn't seem to find the file with fileref

/* Assign the fileref */
filename TPOD "/mnt/share/nPROD_PVTdata/DAM/DAM_OD_07-20-2023-121124/THREE_PART_OFFER_DLY.csv";

/* 4. Import the first CSV file */
data THREE_PART_OFFER_DLY;
    infile TPOD delimiter = ',' MISSOVER DSD lrecl=500 firstobs=2;
    informat BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID best32. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12. RMR_FLAG $1.;
    format BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID 22. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12. RMR_FLAG $1.;
    input BID_TYPE COMBINED_CYCLE_NAME ID PARTICIPANT_NAME RESOURCE_NAME RESOURCE_TYPE RMR_FLAG;
run;

/* Clear the fileref after use */
filename TPOD clear;

I'm going to step away for a bit

[Tom]

Can you run operating system commands from SAS code?

Try seeing what ls says about the file.

data _null_;
   infile 'ls -l /mnt/share/nPROD_PVTdata/DAM/DAM_OD_07-20-2023-121124/THREE_PART_OFFER_DLY.csv' pipe;
  input;
  put _infile_;
run;

PS:  Do not try to store 22 digits in a numeric variable.  There is not enough precision for that.  ID variable should be CHARACTER.

[ccarswell]

1                                                          The SAS System                              17:16 Friday, August 18, 2023

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='DAMTPO.sas';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='Q:\users\ccarswell\DAMTPO.sas';
9          %LET _SASPROGRAMFILEHOST='CCARSWELL';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP(9, 4, 4) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         * Assign the fileref */
29         filename TPOD "/mnt/share/nPROD_PVTdata/DAM/DAM_OD_07-20-2023-121124/THREE_PART_OFFER_DLY.csv";
30         
31         /* 4. Import the first CSV file */
32         data THREE_PART_OFFER_DLY;
33             infile TPOD delimiter = ',' MISSOVER DSD lrecl=500 firstobs=2;
34             informat BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID best32. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12.
34       !  RMR_FLAG $1.;
35             format BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID 22. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12.
35       ! RMR_FLAG $1.;
36             input BID_TYPE COMBINED_CYCLE_NAME ID PARTICIPANT_NAME RESOURCE_NAME RESOURCE_TYPE RMR_FLAG;
37         run;

ERROR: No logical assign for filename TPOD.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.THREE_PART_OFFER_DLY may be incomplete.  When this step was stopped there were 0 observations and 7 
         variables.
MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2023-08-18T17:21:18,518-05:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
_DISARM| | _DISARM| 0| _DISARM| 21655552| _DISARM| 12| _DISARM| 13| _DISARM| 40| _DISARM| 18696| _DISARM| 0.000000| _DISARM| 
0.002290| _DISARM| 2008016478.515734| _DISARM| 2008016478.518024| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
WARNING: Data set WORK.THREE_PART_OFFER_DLY was not replaced because this step was stopped.
PROCEDURE| _DISARM|         STOP| _DISARM| 2023-08-18T17:21:18,518-05:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
_DISARM| 23240704| _DISARM| 21655552| _DISARM| 12| _DISARM| 13| _DISARM| 72| _DISARM| 18712| _DISARM| 0.000000| _DISARM| 0.004657| 
_DISARM| 2008016478.513945| _DISARM| 2008016478.518602| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

38         
2                                                          The SAS System                              17:16 Friday, August 18, 2023

39         /* Clear the fileref after use */
40         filename TPOD clear;
WARNING: No logical assign for filename TPOD.
41         
42         
43         %LET _CLIENTTASKLABEL=;
44         %LET _CLIENTPROCESSFLOWNAME=;
45         %LET _CLIENTPROJECTPATH=;
46         %LET _CLIENTPROJECTPATHHOST=;
47         %LET _CLIENTPROJECTNAME=;
48         %LET _SASPROGRAMFILE=;
49         %LET _SASPROGRAMFILEHOST=;
50         
51         ;*';*";*/;quit;run;
52         ODS _ALL_ CLOSE;
53         
54         
55         QUIT; RUN;
56       

[ballardw]

This tells you that your INFILE statement is using a filename that isn't correct:

32         data THREE_PART_OFFER_DLY;
33             infile TPOD delimiter = ',' MISSOVER DSD lrecl=500 firstobs=2;
34             informat BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID best32. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12.
34       !  RMR_FLAG $1.;
35             format BID_TYPE $4. COMBINED_CYCLE_NAME $32. ID 22. PARTICIPANT_NAME $32. RESOURCE_NAME $32. RESOURCE_TYPE $12.
35       ! RMR_FLAG $1.;
36             input BID_TYPE COMBINED_CYCLE_NAME ID PARTICIPANT_NAME RESOURCE_NAME RESOURCE_TYPE RMR_FLAG;
37         run;

ERROR: No logical assign for filename TPOD.

The Why is in this bit:

28         * Assign the fileref */
29         filename TPOD "/mnt/share/nPROD_PVTdata/DAM/DAM_OD_07-20-2023-121124/THREE_PART_OFFER_DLY.csv";

The comments that start with an * end at a ;  so this filename statement likely was never executed.

Make sure the comment on line 28 starts with /* which then uses everything up to the */ as a comment as was likely intended.

[Reeza]

I don't think this is a timeout issue, this is an issue of not being able to connect/find the file. Can you navigate to this location in the viewer somehow? If so, can you right click, select properties and confirm the path is the same?