Accessing big data uisng SAS PC

[dr2014]

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!

[Kurt_Bremser]

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

[dr2014]

@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;

[Tom]

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? 

[dr2014]

@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;

[Tom]

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?

[dr2014]

@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)

id 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.

[Kurt_Bremser]

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.

[dr2014]

@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.

[Kurt_Bremser]

---

@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.

[dr2014]

@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!

[SASKiwi]

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

[dr2014]

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

[SASKiwi]

I suggest making your post subject a bit clearer then.

[Tom]

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?