topic Re: proc export csv file to excel - error msg in SAS Programming

proc export csv file to excel - error msg

wlierman — Thu, 12 Sep 2024 22:22:45 GMT

I pulled 1,499,754 obs from a business objects warehouse. I ran into the excel row limitation of 1,048,576 rows. I exported the file as a csv to path on the SAS server. Then I used SAS to import the file and split the

Proc import datafile = "M:\Data\OR0206965\2845_CMS_Quarterly 2024_Q2 _ 1486138___9.11.2024.csv"
            out = ANALYZE3.DRTS_2845_OHP_Monitoring
            dbms = csv
			replace;
			getnames=yes;
			run;

Then split the file into two parts

/* SPLITTING CSV FILE INTO PARTS */
/* Split SAS dataset into part 1 */
DATA ANALYZE3.DRTS_2845_Firstpart;
SET ANALYZE3.DRTS_2845_OHP_Monitoring (firstobs = 1 obs = 750000);
run;

/* Split SAS dataset into part 2 */
DATA ANALYZE3.DRTS_2845_Secondpart;
SET ANALYZE3.DRTS_2845_OHP_Monitoring (firstobs = 750001 obs = 1499754);
run;

Everything is straightforward - nothing fancy.

The next step is to export back to my computer so I can send to the original requestor. I'll include the code for the first split (the second is identical).

proc export data=ANALYZE3.DRTS_2845_Firstpart
    outfile = "C:/Users/OR0206965/DRTS_2845_1.csv"
	dbms = csv
	replace;
	sheet="SHEET_1";
run;

The problem shows up in the log

178  proc export data=ANALYZE3.DRTS_2845_Firstpart
179      outfile = "C:/Users/OR0206965/DRTS_2845_part1.csv"
180      dbms = csv
181      replace;
NOTE: The previous statement has been deleted.
182      sheet="SHEET_1";
         -----
         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

183  run;

That is the problem. Likely very simple but I can't see the problem. I have checked against similar proc export code that did run (in another project).

Your corrections | suggestions will be very appreciated.

Thank you

wlierman

Re: proc export csv file to excel - error msg

ballardw — Thu, 12 Sep 2024 22:47:06 GMT

CSV files are flat text files and do not have "sheets". Excel, after it is done reading them treats them as having sheets but the native CSV file that SAS creates does not recognize "sheet". So just remove the SHEET.

Note that if your recipient is going to read the CSV file into a real data management system, or SAS, there is no reason to split the file. That is only an EXCELism with row limits. CSV files per se do not have a row limit.

@wlierman wrote:

I pulled 1,499,754 obs from a business objects warehouse. I ran into the excel row limitation of 1,048,576 rows. I exported the file as a csv to path on the SAS server. Then I used SAS to import the file and split the
Proc import datafile = "M:\Data\OR0206965\2845_CMS_Quarterly 2024_Q2 _ 1486138___9.11.2024.csv"
            out = ANALYZE3.DRTS_2845_OHP_Monitoring
            dbms = csv
			replace;
			getnames=yes;
			run;
Then split the file into two parts
/* SPLITTING CSV FILE INTO PARTS */
/* Split SAS dataset into part 1 */
DATA ANALYZE3.DRTS_2845_Firstpart;
SET ANALYZE3.DRTS_2845_OHP_Monitoring (firstobs = 1 obs = 750000);
run;

/* Split SAS dataset into part 2 */
DATA ANALYZE3.DRTS_2845_Secondpart;
SET ANALYZE3.DRTS_2845_OHP_Monitoring (firstobs = 750001 obs = 1499754);
run;
Everything is straightforward - nothing fancy.

The next step is to export back to my computer so I can send to the original requestor. I'll include the code for the first split (the second is identical).
proc export data=ANALYZE3.DRTS_2845_Firstpart
    outfile = "C:/Users/OR0206965/DRTS_2845_1.csv"
	dbms = csv
	replace;
	sheet="SHEET_1";
run;
The problem shows up in the log
178  proc export data=ANALYZE3.DRTS_2845_Firstpart
179      outfile = "C:/Users/OR0206965/DRTS_2845_part1.csv"
180      dbms = csv
181      replace;
NOTE: The previous statement has been deleted.
182      sheet="SHEET_1";
         -----
         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

183  run;
That is the problem. Likely very simple but I can't see the problem. I have checked against similar proc export code that did run (in another project).

Your corrections | suggestions will be very appreciated.

Thank you

wlierman

Re: proc export csv file to excel - error msg

Tom — Fri, 13 Sep 2024 01:17:51 GMT

Did you want to make a CSV file or an EXCEL file? They are NOT the same thing.

If you want to make a CSV file then do not use the SHEET= statement as that makes no sense.

proc export
  data=ANALYZE3.DRTS_2845_OHP_Monitoring (obs = 750000)
  outfile = "C:/Users/OR0206965/DRTS_2845_1.csv"
  dbms = csv
  replace
;
run;
proc export
  data=ANALYZE3.DRTS_2845_OHP_Monitoring (firstobs = 750001)
  outfile = "C:/Users/OR0206965/DRTS_2845_2.csv"
  dbms = csv
  replace
;
run;

If you want to make an EXCEL file than use XLSX as the DBMS and use xlsx as the extension. Then you can use the SHEET= statement.

Re: proc export csv file to excel - error msg

Tom — Fri, 13 Sep 2024 01:25:19 GMT

If all you wanted to do was split the CSV file into two file why did you make it into a SAS dataset first?

Copy the header first, then append the lines to the appropriate files.

filename bigfile "M:\Data\OR0206965\2845_CMS_Quarterly 2024_Q2 _ 1486138___9.11.2024.csv" ;
filename file1 "C:/Users/OR0206965/DRTS_2845_1.csv";
filename file2 "C:/Users/OR0206965/DRTS_2845_2.csv";

data _null_;
  infile bigfile obs=1;
  input;
  file file1;
  put _infile_;
  file file2;
  put _infile_;
run;

data _null_;
  infile bigfile firstobs=2;
  input;
  if _n_<=75000 then file file1 mod;
  else file file2 mod;
  put _infile_;
run;

If the data lines are longer than 32767 bytes then add LRECL= option to the INFILE and FILE statement.