Most efficient way to create subset from a huge dataset

[DeepakSwain]

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. 

[art297]

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

[DeepakSwain]

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,

[art297]

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

[LinusH]

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.