DATA Step, Macro, Functions and more

Most efficient way to create subset from a huge dataset

Reply
Frequent Contributor
Posts: 104

Most efficient way to create subset from a huge dataset

Hi there,

I am having a dataset which is updated every month with addition of million records. It is having a couple of variables containing free text with length upto 32767. While taking subset using PROC SQL, it takes couple of days. Is there any efficent way to decrease the processing time e.g. using DS2. Please let me. 

 

Thanks in advance. 

 

Swain
PROC Star
Posts: 7,471

Re: Most efficient way to create subset from a huge dataset

Posted in reply to DeepakSwain

The title of your question, and specifics in your description, refer to two totally different operations. Are you taking a subset of a large dataset, or appending new data to an existing file?

 

If it's the latter, you might one to try proc append (see: http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#n1tgk0uanvisvon1r26l... )

 

Also, have you ever done an analsis of the "couple of variables" that have lengths of 32767? You can probably shorten the time quite a bit of you can reduce the lengths of those variables.

 

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 104

Re: Most efficient way to create subset from a huge dataset

Hi art297,

 

I am interested to take subset of an existing dataset. I believe that once the subset is taken, the variable having free text with length $32767, which can not be changed as tool dependent, may be dropped to improve the efficiency. 

 

Thank you in advance for your kind reply. 

 

Regards,

Swain
PROC Star
Posts: 7,471

Re: Most efficient way to create subset from a huge dataset

Posted in reply to DeepakSwain

Is your desired subset just the new records that have been added (i.e., the last x records from a given point), or could they exist anywhere in the dataset? If the latter, I'd suggest using a where option in a set (datastep) or from (sql) statement. If it's the latter, I'd suggest saving the last record number to a data file, then using a firstobs dataset option.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 5,426

Re: Most efficient way to create subset from a huge dataset

Posted in reply to DeepakSwain
If you are using a data step make sure to exclude this column via the drop data set option in the SET statement.
If your subset is relatively small investigate if you can have an index in place that supports your query.
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 144 views
  • 3 likes
  • 3 in conversation