topic Re: Extreme Newbie Question: connecting to DB in New SAS User

Extreme Newbie Question: connecting to DB

TimHenderson — Thu, 05 Dec 2019 16:29:57 GMT

Good morning!

To say that I'm new to SAS is an understatement. While I wish I had more foundational knowledge of databases and programming, the reality is that I do not and my new job requires getting up to speed with SAS. I'm confident I can get there, at some point, but I'm not there yet. Not even close.

I am trying to access a database and have explored various help guides but most assume some base level of knowledge that I simply don't have - so I'm hoping someone here can assist in getting me started. For various reasons, I cannot request someone at my new employer sit down and walk me through everything.

I requested and was granted access to a large database containing roughly 114,000,000 records. The IT staffer emailed me the "libname" to access it:

libname entp odbc noprompt="driver=ODBC Driver 11 for SQL Server; database=entp_rnt; server=dbque2122,2226; trusted_connection=yes" schema="dbo";

Now what? Additionally, I'm told not to "bring a whole table down" because the database is so big. How do I avoid doing that?

I have access to both SAS Enterprise Guide and SAS 9.4.

Furthermore, do you have any recommendations for courses/books that are truly fundamental so I can develop some baseline knowledge of SAS?

Thank you in advance for your help.

Tim

Re: Extreme Newbie Question: connecting to DB

Tom — Thu, 05 Dec 2019 16:39:46 GMT

Did they provide any documentation on what tables (datasets) are in that database? Did they provide any information on the columns (variables) in the tables (datasets)?

What do you need to do with the data? Is there a particular question you are trying to answer? Or statistic you are trying to generate?

To avoid pulling a whole table down make sure you are generating any summaries you need in the data base and only return the summary data instead of the detailed data. One way is to use explicit passthru SQL code in PROC SQL. WIth that you will need to write code that work in the remote database.

To avoid pulling too much data you need to plan ahead and know what type of results you expect your queries to generate. Either by checking the documentation on how many distinct values a variable is allowed to take etc. Or counting before you try to pull the data.

For example here is how you could find the MIN , MAX, number of non-missing values and number of distinct values for a variable X in a dataset Y using pass thru SQL.

proc sql ;
  connect using entp ;
  select * from connection to entp
( select min(X) as min_X
      , max(X) as max_X
      , count(X) as n_X
      , count(distinct X) as distinct_X
  from Y
);

Re: Extreme Newbie Question: connecting to DB

Cynthia_sas — Thu, 05 Dec 2019 16:47:01 GMT

Hi:

Try experimenting with creating some subsets from a few SASHELP datasets before you use ODBC against your big file. For example, this makes a subset of SASHELP.CLASS for the subset of rows where AGE GE 14:

** option 1;
data work.teens_d;
  set sashelp.class;
  where age ge 14;
run;

proc print data=work.teens_d;
  title 'subset created with a dataset';
run;

** option 2;
proc sql;
  create table work.teens_s as
  select *
  from sashelp.class
  where age ge 14;
quit;

proc print data=work.teens_s;
  title 'subset created with a proc sql';
run;

Then eventually, you will do something like described here -- http://support.sas.com/kb/41/888.html which are just variations on the above programs and utilize the LIBNAME engine to ODBC.

Before you do this, I would recommend taking our free Programming 1 e-learning course: https://support.sas.com/edu/schedules.html?ctry=us&crs=PROG1 (click the start button under the e-learning section). You will need to log onto a SAS Profile before you complete the activation process.

adfasdf

Re: Extreme Newbie Question: connecting to DB

TimHenderson — Thu, 05 Dec 2019 16:55:54 GMT

Thank you. I was given a list of "extract columns" (113 in total) but I don't know if any other tables exist beyond a central one (if that makes sense).

I need to be able to do a variety of things with the data, but I can accomplish most of what I need to do by exporting a week's worth a data into Excel until I learn how to use SAS (the data has a column for date). That's roughly 50,000 entries, which while slow, should be able to be handled by Excel.

To begin, without doing anything else, are you suggesting I could enter the code below into the editor to produce a new summary table, where X is one of the extract columns? If I wanted to just pull one day's worth of data, but all the columns/info available for the day, what would you recommend?

Again, my apologies for my newness here.

Re: Extreme Newbie Question: connecting to DB

Reeza — Thu, 05 Dec 2019 17:34:37 GMT

Have you taken the free SAS e-course? I would take the first one and play around with the tasks (not coding). SAS has both a coding and GUI interface the the GUI is pretty straightforward to learn.