ODS CSV Output File Creation Very Slow

spjcdc — Mon, 13 Jan 2014 18:41:04 GMT

I'm creating a CSV output file using ODS CSV and it takes a surprisingly long time to create. It's large, but while it takes less than 17 minutes to read the data from disk and create a subset, it takes 94 minutes to write it out. Before I start investigating whether there are any hardware-related issues that could account for this, is there any SAS-related issues that I should be aware of? Is there an alternative approach that would accomplish the same thing? I chose Proc Print because I need the variables a specific order and I can easily do that. Any other solution would need to be able to the same thing.

My log is below. Thanks for your help.

Steve

109 rsubmit ;

NOTE: Remote submit to TASK1 commencing.

63 libname spj1 "\\cdc\csp_private\m103\spj1" ;

NOTE: Libref SPJ1 was successfully assigned as follows:

Engine: V9

Physical Name: \\cdc\csp_private\m103\spj1

64 options obs=max ;

65 Data spj1.VaxData ;

66 set &Grantee..Vaccination

67 (Keep= CDCID VaxGroup Vaxdate MVX CVX CPT TradeName LotNum Route Site RecordType

68 VaxComplete VaxRefusal IISValidity ProvID V_Eligibility VaxEntDate ProvChar) ;

69 where VaxGroup = 6 ;

70 retain VaxRecordNum 0 ;

71 VaxRecordNum + 100 ;

72 MatchNum = 1 ;

73 Length _PatientID $15 _VaxDate $8 _VaxEntDate $8 LotNum1-LotNum3 $1 ;

74 _PatientID = scan(CDCID,3,'-') ;

75 _VaxDate = put(VaxDate,yymmddN8.) ;

76 _VaxEntDate = put(VaxEntDate,yymmddn8.) ;

77 call missing(lotNum1, LotNum2, LotNum3) ;

78 run ;

NOTE: There were 4850003 observations read from the data set BAA.VACCINATION.

WHERE VaxGroup=6;

NOTE: The data set SPJ1.VAXDATA has 4850003 observations and 26 variables.

NOTE: DATA statement used (Total process time):

real time 16:56.65

cpu time 3:02.39

81 ods _all_ close ;

82 ods tagsets.csv file="\\cdc\csp_private\m103\spj1\VaxData.CSV" ;

NOTE: Writing TAGSETS.CSV Body file: \\cdc\csp_private\m103\spj1\VaxData.CSV

84 Proc Print data = spj1.VaxData noobs ;

85 Var _PatientID VaxRecordNum MatchNum _Vaxdate MVX CVX CPT TradeName LotNum1-LotNum3 Route

85 ! Site RecordType VaxComplete

86 VaxRefusal IISValidity ProvID V_Eligibility _VaxEntDate ProvChar ;

87 run ;

NOTE: There were 4850003 observations read from the data set SPJ1.VAXDATA.

NOTE: PROCEDURE PRINT used (Total process time):

real time 1:34:00.66

cpu time 1:32:46.20

89 ods _all_ close ;

NOTE: Remote submit to TASK1 complete.

Re: ODS CSV Output File Creation Very Slow

Reeza — Mon, 13 Jan 2014 18:58:41 GMT

Use a data _null_ step to manually write the CSV file.

There's an answer on here in the past two weeks related to this question.

Re: ODS CSV Output File Creation Very Slow

Tim_SAS — Mon, 13 Jan 2014 19:21:31 GMT

Reeza's suggestion is a good one and probably the fastest. However if you want to stick with ODS then there is a tagset called SIMPLECSV that may be a bit faster than the standard CSV tagset. The difference between the standard CSV tagset and SIMPLECSV is that SIMPLECSV is treats currency and date values as strings instead of numbers and may quote other kinds of strings that are actually numbers. YMMV.

ods tagsets.simplecsv file="whatever";

Re: ODS CSV Output File Creation Very Slow

Vish33 — Wed, 15 Jan 2014 09:46:15 GMT

As Reeza and Tim said it's better to use data _null_ step to create csv files . I am also using the same to create. Here is my code:

data _null_;

set result_&CIRC ;

file "/sasdata/test.csv"

dlm="^" lrecl=20000;

length head1 $2000.;

head1 = "&variable_names";

if _n_=1 then do;

put head1

/*head2 head3 head4 head5*/

;

end;

put @1

&variable_names;

run;

topic Re: ODS CSV Output File Creation Very Slow in ODS and Base Reporting

ODS CSV Output File Creation Very Slow

Re: ODS CSV Output File Creation Very Slow

Re: ODS CSV Output File Creation Very Slow

Re: ODS CSV Output File Creation Very Slow