Exporting from sas to sql in SAS Enterprise Guide 8.3.

[SAS_V_R]

Greetings and thank you in advance.

I am using SAS Enterprise Guide 8.3.

Without splitting, splitting and appending. That means no split files to append later. I have big, long files. My 4 questions are in bold: 

I need to export my SAS data output of 1,790,740 into a table.

--Question 1: How do I export SAS data output of 1,790,740 into sql, sql table? 

****Yes, I am aware there is an excel limit of 1 million or so. Yes, exporting it into csv and txt was not successful because of the limit so I am asking about exporting it into SQL as an ALTERNATIVE OPTION, IF AT ALL. ****

I need to export my SAS data output of 2,214,278 into 1 table.

--Question 2: How do I export SAS data output of 2,214,278 into sql, sql table? 

****Yes, I am aware there is an excel limit of 1 million or so. Yes, exporting it into csv and txt was not successful because of the limit so I am asking about exporting it into SQL as an ALTERNATIVE OPTION, IF AT ALL.****

Question 3: How can the export be done such that the table of 1,790,740 rows is 1 table and not split tables that I would need to append later?

Question 4: How can the export be done such that the table of 2,214,278 rows is 1 table and not split tables that I would need to append later?

When I export this to CSV. I cannot. I am not able to. This is because the data set is too large for the excel grid and I will lose the data that was not loaded. This is the error message I get. When I export this to TXT.  I get the same error message.

[Tom]

What does "EXCEL GRID" have to do with a CSV file?  I CSV file is just a TEXT file.  It is totally unrelated to Excel.

[SAS_V_R]

Tom, stop spamming me. It is disrespectful. If you do not understand the questions, you can ask me to clarify the questions I have or not write anything. 

[AlanC]

Whoa, Tom's question was fine. Not sure what you are referring to. A CSV or TXT file are 100% unrelated to Excel. If you have Excel tied to open CSV and TXT files, that is a Windows issue, not SAS. I don't understand the question and 100% do not 'spam' people. If you want to see a CSV file that is super large, use something like UltraEdit or Notepad++. Not sure why you want to open a file that large. Excel handles a million rows. 

[SAS_V_R]

You don't understand what I am asking?

[Tom]

If you need help with reading a CSV file into SQL server you might want to ask on a site about SQL server.

If you want to check your CSV that you generated from SAS and you don't have an editor that can view it you can always just use a SAS data step to look at the file.  For example to check the first 5 and the last line of the file you might do:

data _null_;
  infile 'myfile.csv' end=eof;
  if _n_ in (1:5) or eof then list;
run;

If you have SAS/Access then perhaps you can transfer the data directly without using a CSV file.

Another thing to be wary of when using a CSV file for transfer is that it loses the metadata about the variables.  The only metadata you can put into a CSV file is the row header, which could be used to share the variable names.  But there is no place to code the type, length or other information such as things like:  valid values, is it a date value, a time value, a datetime value.  Or any codelists/display formats that might be associated with the variables.

[SAS_V_R]

Thanks for the information. When i export my SAS output to CSV, it cuts off rows/observations at row 500,000 or so. That means I cannot see all of the data. MY question is--how do I see all of the data on 1 sheet?

[AlanC]

The limitation is with Excel. You may be running out of memory on your machine. What version of Excel do you have? Excel can handle 1M rows but that is capped by your physical memory on your PC. I am pretty sure it can 'virtually' hold more than that if it is in a DB table somewhere but it gets complex to set up.

[Quentin]

What do you mean by 'export to a SQL table or any other grid/table'?

Excel has a limit on number of rows in a worksheet (1,048,576 rows for an .xlsx).

If you have a database, and SAS/ACCESS licenses to connect to the database, then creating a new database table from a SAS dataset is straight forward via PROC APPEND or a DATA step.

[Tom]

To write data into a SQL sever table from SAS the best way is to connect your SAS session to the SQL server database.

So something like this:

libname out sqlsvr <connection-options> <LIBNAME-options>;
libname in 'location where your SAS dataset lives';
proc append data=in.mydata base=out.mydata force;
run;

If you don't have SAS/Access to SQL server licensed but you do have SAS/Acess to ODBC then perhaps you can use that instead.

You will have to ask on an SQL Server forum is the Excel limit of 1 million rows has any impact on SQL servers ability to load a CSV file with 1 or 2 or 3 million or more rows.  I would doubt it would.

But if you really want to export data to CSV and impose a limit on the number of lines per file that is not hard to do.

Here is example template of how to write such a program.

data _null_;
do row=1 to 1000000;
  set in.mydata ;
  fileno+1;
  length filename $256 ;
  filename=cats('path to write the file/basename',fileno,'.csv');
  file out dsd filevar=filename ;
  if row=1 then put 'var1,var2,var3';
  put var1 var2 var3;
end;
run;  

[SAS_V_R]

Thank you. That was helpful. I want it more than a million rows.  I want two million five hundred thousand rows. Is this possible Tom?

[Tom]

---

@SAS_V_R wrote:

Thank you. That was helpful. I want it more than a million rows.  I want two million five hundred thousand rows. Is this possible Tom?

---

I don't see why loading a CSV into SQL Server would work any differently for 2 millions rows than it does for 10 rows.

Just don't try to open the file with Excel.

Here is page found easily with Google by searching for loading large CSV files into SQL server.

https://dba.stackexchange.com/questions/186121/import-data-from-48-gb-csv-file-to-sql-server

[SAS_V_R]

Tom, here's the problem. I had a file--it was very, very large. When I used my sas enterprise to EXPORT this file--it gave me an error message many, many times.

This was the error message "This data set is too large for the excel grid. IF you save this workbook you'll lose the data that wasn't loaded."

In sas enterprise, if I go under share, export, choose file type text files csv --it does not upload the amount I need, it uploads less. Less is the excel limit. Do you know what I mean?

[Tom]

---

@SAS_V_R wrote:

Tom, here's the problem. I had a file--it was very, very large. When I used my sas enterprise to EXPORT this file--it gave me an error message many, many times.

This was the error message "This data set is too large for the excel grid. IF you save this workbook you'll lose the data that wasn't loaded."

In sas enterprise, if I go under share, export, choose file type text files csv --it does not upload the amount I need, it uploads less. Less is the excel limit. Do you know what I mean?

---

Then don't use Enterprise Guide to make the CSV file.  Use actual SAS code instead.

filename csv 'some location on your SAS server/mydata.csv';
proc export data=in.mydata out=csv dbms=csv;
run;

If you need to move the file from the SAS server back to the machine where you are running Enterprise Guide then use then use the File Copy task in Enterprise Guide to move the file.

[SAS_V_R]

Tom, the machine I am using is my work computer. What other machine could it be?