topic Re: Accessing big data using SAS PC in SAS Data Management

Accessing big data uisng SAS PC

dr2014 — Sun, 29 Apr 2018 11:27:37 GMT

Hi all,

I am working with big data the size of around 158,000,000 KB . I am using SAS EG on PC. Its taking a long time to bring in the data. I will be using 'views' to work with the data and also limiting the data to few required variables.

What are some of the things I should take into account while working with 'views' and/or other ways I could work with the data faster? Your inputs will be really helpful. Please let me know as soon as possible. Thanks!

Re: Accessing big data uisng SAS PC

Kurt_Bremser — Sun, 29 Apr 2018 15:18:16 GMT

Define "bring in the data". Do you mean reading a SAS dataset for analysis, or importing the data from external sources?

Re: Accessing big data using SAS PC

dr2014 — Sun, 29 Apr 2018 16:04:23 GMT

@Kurt_Bremser sure. I am working with a sas dataset the size of 186 GB. The 'view' for the sas dataset 'have' gets created instantly but the run time for the next datastep doesn' t seem to end. Below is the code I am running presently. I selected both the data steps to run and its about 20 minutes now and its still running.

data want(keep=id var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11) /view=want;

set libref.have;

run;

data want_2;

set want;

if id=. then id2=0;

else id2=1;

run;

Re: Accessing big data using SAS PC

Tom — Sun, 29 Apr 2018 16:13:34 GMT

Don't make a copy of the large data if you can avoid it. What you have posted as two steps could all be done in the first step.

data want /view=want;
  set libref.have(keep=id var1-var11) ;
  id2 = not missing(id);
run;

What are you doing to DO with the data?

Re: Accessing big data using SAS PC

dr2014 — Sun, 29 Apr 2018 16:47:54 GMT

@Tom thanks for the advice. I actually need to work a lot more with these views. Doing a proc sort on the view is also time consuming. Is that normal?

For e.g: for the view 'want'

proc sort data=want out=want_sort; by id; run;

Re: Accessing big data using SAS PC

Tom — Sun, 29 Apr 2018 16:50:36 GMT

You are again making a copy of the data.

If you need the data sorted in a way it is not already sorted then you might look at using an INDEX instead.

I am not sure that an index can be used with a view however.

Do you really need to sort the data?

What are you ultimately trying to do?

Re: Accessing big data using SAS PC

dr2014 — Sun, 29 Apr 2018 18:14:50 GMT

@Tom I get your point but I have a larger task at hand. The system is not built to handle big data and I am thinking of ways to process it faster. Here is a detailed list of my task. Please advice me on what's the best way to go about it:

(1) I have 5 large sas datasets orig1 to orig5 around 186 gb size each . I just need a few variables from each of these datasets with non-missing ids for which I would like to create views called orig_1v to orig_5v;

So the code will be. Creating a view allows me to get what I need instantly.

data orig_1v (keep=id start_date2 end_date type)/view=orig_1v;

set orig1;

where id ne .;

run;

Below is the sample of each of the orig_1v to Orig5v views (please ignore the last 2 empty columns)

start_date2

end_date

type

(2) I need only certain ids from orig_1v to orig_5v which are present in the dataset ‘required’. Below is the sample of the ‘required’ sas dataset

id	start_date
001	1/1/2001
002	1/2/2002
003	1/2/3002
004	1/3/2002

(3) I can either stack the views first (which I don't know if it’s a good idea to do that):

data orig_vall;

Set orig_1v

orig_2v

orig_3v

orig_4v

orig_5v;

run;

(4) Then I can do an inner join to create another view:

proc sql;

create view as want

Select a*.,b.start_date

from orig_all as a

Inner join required as b

on a.id=b.id;

quit;

Once I have the view ‘want’ I have to create derived variables and flag them based on conditions related to the dates:

start_date, start_date2 and end_date. Could I do that using the view 'want'?

Is this a good approach? Please let me know. Thanks.

Re: Accessing big data using SAS PC

Kurt_Bremser — Sun, 29 Apr 2018 19:03:42 GMT

As the first step, create new datasets (not views) that only contain the needed variables. These will sort a lot faster.

The first step will of course take lots of time, but a sequential read will be the fastest you can do.

Take a look at the overall data rate of your storage. With data this size, you need something that can deliver 500 MB/s or faster.

Re: Accessing big data using SAS PC

SASKiwi — Sun, 29 Apr 2018 21:17:59 GMT

If you have SAS on a remote server available to you it would be far quicker there.

Re: Accessing big data using SAS PC

Tom — Sun, 29 Apr 2018 21:34:41 GMT

Since (2) seems to be the part that will subset the data the most do that first.

How large is this list? Can you fit it in a macro variable?

Can you fit it in memory?

Re: Accessing big data using SAS PC

dr2014 — Sun, 29 Apr 2018 21:36:15 GMT

@Kurt_Bremser thanks for your suggestion. I will do that. How is it possible to estimate the overall data rate of storage? I would like to see that and provide information to our technical analyst in case we need any changes. Please let me know.

Re: Accessing big data using SAS PC

dr2014 — Sun, 29 Apr 2018 21:37:29 GMT

It is on a remote server but its not helping much with the run time.

Re: Accessing big data using SAS PC

dr2014 — Sun, 29 Apr 2018 21:58:50 GMT

@Tom I decided to use a macro to see how it works. Referring to the different librefs below is my code.I am not sure if it fits in memory, but it would be worth trying.

%macro allfiles(varlist=);

libname2.orig1&varlist

libname2.orig2&varlist

libname3.orig3&varlist

libname4.orig4&varlist

libname5.orig5&varlist

libname5.orig6&varlist

%mend;

data out_all;

set %allfiles(varlist=%str((keep=id var1 var2 type);

run;

Re: Accessing big data using SAS PC

SASKiwi — Mon, 30 Apr 2018 00:33:30 GMT

I suggest making your post subject a bit clearer then.

Re: Accessing big data using SAS PC

Tom — Mon, 30 Apr 2018 01:06:52 GMT

Macro code is not going to change the SAS code that is run or how SAS runs it. It just makes it easier for you type it. For just 5 or 6 datasets if is just is probably easier to just use copy/paste to replicate the code rather than complicate your program with macro code.

Most SAS jobs are I/O bound and it sounds like that is your problem. Run PROC CONTENTS on your input datasets and check some things. Make sure they were made with the same operating system and version of SAS. If SAS has to use CEDA to read the files it will slow it down a lot. Check if they are already sorted by the ID variable(or at least if SAS thinks so). Eliminating the need to sort is a hugh deal. Check if they are using SAS compression. Many SAS datasets can get 60-80 reduction in disk size just by using the COMPRESS=BINARY option when creating them.

Try using the SPDE engine to store your new subsets. That can really reduce the disk space needed to store the data.

The main thing is subset as early as possible.

If your list of ID values is small then store them in a macro variable.

proc sql noprint;
 select distinct id into :idlist separated by ' '
 from relation
 ;
quit;

%let varlist=id var1 vara varb varc ;
proc sort data=libname1.orig1(keep=&varlist) out=subset1 (compress=binary);
  by id;
  where id in (&idlist);
run;
...
data combine/view=combine;
  set subset1 subset2 subset3 .... ;
  by id;
run;

data final (compress=binary);
  merge relation combine ;
  by id;
run;

Re: Accessing big data using SAS PC

Kurt_Bremser — Mon, 30 Apr 2018 04:40:34 GMT

@dr2014 wrote:

@Kurt_Bremser thanks for your suggestion. I will do that. How is it possible to estimate the overall data rate of storage? I would like to see that and provide information to our technical analyst in case we need any changes. Please let me know.

Just do a

options fullstimer;

data _null_;
set yourlib.yourdata;
run;

and look at the log. Divide the size by the time, and you have your sustained read throughput.

If you pull data from a remote DBMS, the network will be your bottleneck. In this case, unload as much of the initial "slicing" to the database, with explicit pass-through, if needed.

Re: Accessing big data using SAS PC

dr2014 — Mon, 30 Apr 2018 15:18:30 GMT

@Tom Thanks for all your suggestions. I will definitely be implementing them. I have about 20000 ids so it will be hard to put them in a macro. It would have certainly helped to subset to those ids in the first step itself.

Re: Accessing big data using SAS PC

dr2014 — Mon, 30 Apr 2018 15:22:20 GMT

@Kurt_Bremser I will check use your code to check on the time. Thanks for all your advice. Basically, more than 1 person has contributed on the solution. Thanks much!