SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Accessing big data uisng SAS PC

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 146
Accepted Solution

Accessing big data uisng SAS PC

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!


Accepted Solutions
Solution
‎04-30-2018 11:19 AM
Frequent Contributor
Posts: 146

Re: Accessing big data using SAS PC

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

View solution in original post


All Replies
Super User
Posts: 9,886

Re: Accessing big data uisng SAS PC

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 146

Re: Accessing big data using SAS PC

Posted in reply to KurtBremser

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

 

Super User
Super User
Posts: 7,932

Re: Accessing big data using SAS PC

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? 

Frequent Contributor
Posts: 146

Re: Accessing big data using SAS PC

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

Super User
Super User
Posts: 7,932

Re: Accessing big data using SAS PC

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?

 

Frequent Contributor
Posts: 146

Re: Accessing big data using SAS PC

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

Super User
Posts: 9,886

Re: Accessing big data using SAS PC

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.

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 146

Re: Accessing big data using SAS PC

Posted in reply to KurtBremser

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

Super User
Posts: 9,886

Re: Accessing big data using SAS PC


@dr2014 wrote:

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 146

Re: Accessing big data using SAS PC

Posted in reply to KurtBremser

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

Super User
Posts: 3,857

Re: Accessing big data using SAS PC

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

Frequent Contributor
Posts: 146

Re: Accessing big data using SAS PC

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

Super User
Posts: 3,857

Re: Accessing big data using SAS PC

I suggest making your post subject a bit clearer then.

Super User
Super User
Posts: 7,932

Re: Accessing big data using SAS PC

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?

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 325 views
  • 3 likes
  • 4 in conversation