topic Re: CSV not loading all records after Proc Export in SAS Programming

CSV not loading all records after Proc Export

wlierman — Tue, 15 Jun 2021 18:04:28 GMT

I have a SAS dataset of 1,694,321 obs. I wanted to convert into CSV and send to another party. The log of the export is here.

NOTE: The file 'I:\Health
      Analytics\AMB_INFORMATICS\ORPHEUS_ONE_Syntax_Log_MM\Merged_File\Merged_ORPHEUS_ONE_6.15.2021.csv' is:
      Filename=I:\Health Analytics\AMB_INFORMATICS\ORPHEUS_ONE_Syntax_Log_MM\Merged_File\Merged_ORPHEUS_ONE_6.15.2021.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=15Jun2021:10:49:23,
      Create Time=15Jun2021:10:49:23

NOTE: 1694322 records were written to the file 'I:\Health
      Analytics\AMB_INFORMATICS\ORPHEUS_ONE_Syntax_Log_MM\Merged_File\Merged_ORPHEUS_ONE_6.15.2021.csv'.
      The minimum record length was 22.
      The maximum record length was 118.
NOTE: There were 1694321 observations read from the data set SASONE.OR_ONE_ALL_MERGE5A.
NOTE: DATA statement used (Total process time):
      real time           2.50 seconds
      cpu time            2.28 seconds


1694321 records created in I:\Health
Analytics\AMB_INFORMATICS\ORPHEUS_ONE_Syntax_Log_MM\Merged_File\Merged_ORPHEUS_ONE_6.15.2021.csv from
SASONE.OR_ONE_ALL_MERGE5A.


NOTE: "I:\Health Analytics\AMB_INFORMATICS\ORPHEUS_ONE_Syntax_Log_MM\Merged_File\Merged_ORPHEUS_ONE_6.15.2021.csv" file
      was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           2.71 seconds
      cpu time            2.36 seconds

When I go out to the file and test whether it is being read into excel it doesn't load completely but stops after loading 1,048,576 obs. I thought a csv file could be loaded into excel without running into a size constraint.

What is the best method to solve this problem?

Thanks.

wklierman

Re: CSV not loading all records after Proc Export

Reeza — Tue, 15 Jun 2021 18:19:32 GMT

Excel has a size limit itself and that's what you're running into here 🙂

You can load a larger CSV file into Excel in some ways, usually using a DSN connection but this isn't a SAS issue.

https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

Set up a connection to a CSV file:

https://excel.officetuts.net/examples/open-large-csv

Re: CSV not loading all records after Proc Export

wlierman — Tue, 15 Jun 2021 18:31:25 GMT

Thank you. It is the excel side of things.

Thanks again.

wklierman

Re: CSV not loading all records after Proc Export

Kurt_Bremser — Tue, 15 Jun 2021 18:41:24 GMT

IMO, there's only one reasonable thing a SAS user can do with Excel: deinstall it.

Re: CSV not loading all records after Proc Export

wlierman — Thu, 17 Jun 2021 21:20:26 GMT

Thank you. My thoughts precisely.