topic Re: Splitting one table into Multiple tables in a faster way in SAS Procedures

Splitting one table into Multiple tables in a faster way

SonyTrinesh — Mon, 27 Jul 2015 13:17:38 GMT

Hi All,

I have a dataset with contains million reocrds and i want to split the table into multiple tables based on one field with out using Data step.If we can do it Sql that is very much usefull for us.

why i'm preferring sql is in data step while splititng the data it is taking so much time but my intension is to reduce the execution time and workspace also.So can some one suggest if we have any other ways to split the data into mulitiple ways in a faster way.

Re: Splitting one table into Multiple tables in a faster way

RW9 — Mon, 27 Jul 2015 13:55:37 GMT

SQL will not be any quicker that datastep. in fact, it is likely to be slower within SAS. I would suggest you start by posting your datastep code. There maybe ways to reduce the processing if we see your code. For instance have you tried, something like:

proc sql;

create table LOOP as

select distinct THE_SPLIT_VAR

from HAVE;

quit;

data _null_;

set loop;

call execute('data '||strip(the_split_var)||'; set have (where=(the_split_var="'||strip(the_strip_var)||'")); run;');

run;

The above will create a small datastep for each distinct split, with a small where on the read in.

Re: Splitting one table into Multiple tables in a faster way

Reeza — Mon, 27 Jul 2015 14:51:24 GMT

1 million records should be nothing for SAS to process and you shouldn't need to split it up.

That being said, a hash method is usually the fastest to run.

See samples of codes of various methods here:

Split Data into Subsets - sasCommunity

If your data is sorted a call execute is another good way, but you should post your code that you've tried so far.

Re: Splitting one table into Multiple tables in a faster way

SonyTrinesh — Tue, 28 Jul 2015 11:15:31 GMT

Below is the code i've used for spliting the table into multiple tables.

DATA ONE;

INPUT ID $ ;

CARDS;

;

RUN;

DATA TWO THREE FOUR;

SET ONE;

IF ID='1' THEN OUTPUT WORK.TWO;

IF ID='2' THEN OUTPUT WORK.THREE;

IF ID='3' THEN OUTPUT WORK.FOUR;

RUN;

Can some one suggest faster way to split the tables in Data step or in Sql.

Re: Splitting one table into Multiple tables in a faster way

Patrick — Tue, 28 Jul 2015 11:38:13 GMT

That's basically how you would do it and nothing will be faster - except you would want to use "ELSE" as well and if you want to take it to the "extreme" you would order your "if.. then... else.." statements along "probability" having the most likely cases first.

...but as others already wrote: A million records is "nothing" so splitting up the data set feels like the wrong thing to do.

Re: Splitting one table into Multiple tables in a faster way

ballardw — Tue, 28 Jul 2015 16:03:50 GMT

I don't know if data set options would run faster than if then else

DATA TWO (where=(id='1')) THREE (where=(id='2')) FOUR (where=(id='3'));

SET ONE;

RUN;

Or a slightly less verbose way to get the equivalent of if then else which gets better looking if doing 10 statements

DATA TWO THREE FOUR;

SET ONE;

select (id);

when('1') OUTPUT TWO;

when('2') OUTPUT THREE;

when('3') output FOUR;

otherwise Put "Unexpected value for ID" id;

end;

RUN;

Re: Splitting one table into Multiple tables in a faster way

Demarez — Sun, 19 May 2019 06:34:26 GMT

What if my variable that I want to distinct is a date ? Is there any specific program for that ?

Re: Splitting one table into Multiple tables in a faster way

Astounding — Sun, 19 May 2019 09:10:36 GMT

One additional step that sometimes helps: fix the data. You will have to check whether this applies here.

Depending on how the original data was created, it's possible that character variables use much more space than they should. For example, perhaps a variable named GENDER should be "F" or "M" but is actually defined as $200 characters long. That's a problem. Moving around data that is 200 times bigger than it needs to be takes significantly longer.

Compressing the data can reclaim the storage space, but actually takes longer to process. Each time you use the data it needs to be uncompressed, adding to the processing time. Also worth noting: even if the original data set is compressed, that doesn't mean that the subsets will be compressed.