Importing a large CSV file and getting Insuffcient Memory

[SamMad]

Got it. I will run the code by adjusting the INFILE command and share you the results.
Just to provide more background....SAS Environment is being proposed to us by our IT folks as our SQL Server is being completely lock down and IT will not give access to any data outside of an application interface. Me and my couple of colleagues are tech savvy and were running SQL Queries, SSIS packages and all that access is going away soon. So with our large data sets, SAS is the only platform that we are offered that can handle large data sets, has somewhat decent built in tools for ETL, BI and Data management for Business/Tech users.

[TomKari]

Interesting...you're moving to an environment very similar to the one that I supported for many years. To me, there are two big advantages I hope you'll see from moving to the new SAS environment.

First, Enterprise Guide connecting to SAS on a server gives you the user interface on your PC, but with the heavy lifting done on the server. Make sure that there is a very fast connection between your SAS server and your SQL Server environment.

Second, I promise that the SAS ETL, BI, and data management tools are phenomenal. I've specialized in them for twenty years now. I know that it's a pain to learn new stuff (I'm working on a Python project, and believe me, I'm suffering!), but I think you'll find it's effective for your needs.

If something seems wrong, or doesn't make any sense, post to this forum. There's lots of smart people on it!

Tom

[SamMad]

Yes. I am glad you understand my frustration (little? :)) but this is the only out of the box solution that has all the bells and whistles that comes close to SQL type of env with built in BI and more. Python is next on my list. I truly appreciate all of your support to help me dig deep into SAS lake and don't drown....LOL

[Reeza]

Except Python doesn't handle large data as well without some serious horse power under the hood, you'd have to change your code to make it work with said power which annoys the hell out of me and you still have data import issues in every single programming language. I program in R daily and have similar issues - they're all the same and just a tool to get a job done.

[TomKari]

Yes, but there's one big advantage...this is a volunteer project, and unfortunately there's no money to pay for SAS! 😞

So I'm doing the best I can with Python. To be honest, I'm finding it very interesting, and in many ways complimentary to SAS.

If we EVER get to meet up at SGF I'll tell you all about my experiences!

Best,

   Tom

[Reeza]

Depending on the volunteer project scope, SAS UE can be used as well - I've used it for Data for Good/DataKind projects.

I'll admit RMarkdown is my favourite thing ever 😉 I like the ability to have or not have code, the dynamic of the text and comments and having the analysis all neatly laid out. I find tidyverse very similar to SAS in many respects - at least the thinking process.

I'm less familiar with Python, just the basics mostly. Would definitely love to have a conversation if we ever meet up!

[Kurt_Bremser]

---

@TomKari wrote:

If we EVER get to meet up at SGF I'll tell you all about my experiences!

Best,

   Tom

---

Sadly, this would be 2022 in San Diego at the earliest. Or you set up a Webex for us 😉

[Reeza]

SuperUsers virtual drinks?

[Kurt_Bremser]

---

@Reeza wrote:
SuperUsers virtual drinks?

---

I'm all for it. During the COVID lockdown, we had a "virtual department beer" one time via Webex.

[SamMad]

Success. When I changed the infile to the location of the CSV , my code ran successfully and was able to read the 6 records. No issues.
Note: 6 records were read from the infile…..
The Minimum record length was 101
The Maximum length was 198
Note: Data statement used ( Total process time):
real time 0.09 seconds
cpu time 0.00 seconds

What do we do now? How do I proceed on the troubleshooting?

[TomKari · Accepted solution]

Glad to hear you made it this far! Like every other high-end product these days, SAS has multiple ways to import .csv files.

1. Using the "File | Import Data" dialogue in Enterprise Guide. In your case, DON'T use this, as it wants to examine the data on your local PC, and of course your data is on the server. This is a great option if you have a small dataset on your PC.

2. SAS has a software tool called "PROC IMPORT" which will bring data from a variety of formats into SAS. I was able to import a .csv version of the sashelp.cars dataset using this syntax:

proc import datafile="C:\ddd\cars.csv" out=cars dbms=csv;
run;

One nice thing about this option is it prints in the log the SAS code that it generates, so you can grab it and tailor it to your needs.

I suggest you give that code a try, and see what you think.

3. Just write plain old SAS code. This is my option of choice, since i) I've been around SAS since before .csv files existed, or PROC IMPORT or Enterprise Guide, for that matter, and ii) it gives me exquisite control over what's happening with my data. Comparing with the massive code generated by PROC IMPORT, I can pull in my .csv of the sashelp cars dataset with just

data work.cars;
length Make $13 Model $39 Type $6 Origin $6 DriveTrain $5 MSRP 8 Invoice 8 Engine_Size 8 Cylinders 8 Horsepower 8 MPG_City 8 MPG_Highway 8 Weight 8 Wheelbase 8;
infile "C:\Users\Tom\Desktop\cars.csv" lrecl=32767 dsd firstobs=2;
input Make Model Type Origin DriveTrain MSRP Invoice Engine_Size Cylinders Horsepower MPG_City MPG_Highway Weight Wheelbase;
run;

I know I'm kind of shoving a firehose up your nose and turning it on, it's a lot to learn. Ponder on and read about these, and come back with questions. I'm not going to be happy until you tell me you love SAS.

Tom

[SamMad]

Tom,

Thank you. This is exactly what I did and used the prod import option to finally load the data. One thing, I kept on running into is that it didn't like the name and/or out location. I had to change the name/location too many times before it finally took it.
My final successful code looked like this
libname1 saslib1 "/sas/data/dept/info/dev
%Let dir=/sas/import/Dev_shared/Dev_data
proc import datafile="&dir.ABC.csv" out =saslib1.ABC dbms=csv replace;
getnames=yes;
run;

It did encounter some errors on data transformation, e.g. one fo the data column was named Income and it had non numeric values as blanks, NA etc so it flagged all those records for errors but it didn't drop those rows from the final data set. I really like this feature that it gave warnings but didn't fail or kill the process all together.
Now the Q is what is the difference between declaring variables such as Libname vs %Let and when one should use one or other.

Can this generated code be saved and used for improting other data sets over and over again? Like Importing from SQL Server, Excel, Access? Just keep on changing the dbms part? is it really that easy?
My whole idea to use SAS is to use it as a replacement for SQL Server as our IT is locking the Tech assets. So in this case, can SAS truly can be used as Relational Data engine and reporting/presentation tool for my customers.
Over the next few days, I will need a lot of help and support when I start to build my entire data repository in SAS and ditching SQL.
Your help and guidance is much appreciated Tom and it will be needed more going forward.
Thanks
SamMad

[Reeza]

SamMad you can definitely replace SQL with SAS. You should request explicit pass through though so that you can use your SQL as is for now without having to fully translate everything immediately.

[SamMad]

Reeza,
Thanks! I have only been into the SAS world for less than 5 days. LOL
I have huge learning curve but I am encouraged by the fact the community here is so strong and supportive that I will be able to learn and grown my SAS skills pretty quickly and start using it in PROD env in my org in the next few days to weeks. Next few things in my pipeline are:
1. Reuse the Import PROC code for different file types especially if I want to have a direct ODBC connection to SQL Env to start ingesting the data in SAS
2. Build Queries and Data Views by connecting multiple tables/SAS datasets
3. Use SAS as a Reporting layer for my end users so some extent or if that's not possible then be able to build OLAP cubes so it can used for other BI tools such as Excel, Tableau, MicroStrategy etc.
Need to accomplish a lot in short amount of time... Keep sharing and educating me
Really appreciate ALL of your help and support.

[Kurt_Bremser]

Re: use SAS as a relational engine:

Yes, you can, but (IMO) you shouldn't. While you will keep your base tables in a more or less normalized structure, don't waste time to set up integrity rules and constraints. That has already been done in the source DBMS, and the data you get will reflect this.

Re: reporting/presentation tool

Yes, yes, yes! Although you will find that SAS SQL misses some (or a lot) of the options provided in the SQL dialect you are used to, all the other tools (DATA and PROCEDURE steps) are much more powerful for data warehousing purposes than MS SQL and its brethren can ever be.

For reporting, your prime partners will be the DATA step for preparation, and the SUMMARY, TABULATE and REPORT procedures coupled with ODS (the Output Delivery System).

Since you seem to operate in an environment that is not so much different than what I am working in, here some assumptions I make:

• your data comes mostly from a database source
• it is desired to decouple the reporting/analyzing/mining activities from the production database
• your data arrives (or will arrive) in a more or less consistent form (unload files from the database)

To further help us to help you on your path, it will be very useful if you share some information about your SAS setup. We already know that you have a client/server setup with UNIX on the backend; what is available there on top (Web Report Studio, Stored Processes, OLAP), and which SAS modules are licensed?

Run a PROC SETINIT to see the license; if you want, post the log, but discard all site-specific information, keep only the list of modules.

What would I suggest to study next?

The most powerful tool for data preparation is the DATA step; I suggest to start here:

DATA Step Processing 

and here:

Language Reference: Concepts 

For the Procedures, I suggest Base SAS Procedures

Feel free to ask "How do I do this in SAS?" You will need a lot of pointers at first for this kind of question