Solved
Contributor
Posts: 42

Split up a table into multiple tables faster

Is there a simple way in a data step to split up a table into multiple tables?

This macro works. Just wondering if it could be done faster.

data total;

do id = 1 to 1000; do m = 1 to 240; output; end; end;

run;

%macro total (i,ct);

%do i = &i. %to &ct.;

proc sql;
create table total_&i. as
select *
from total
where m = &i.;
quit;

%end;

%mend total;

%total(1,240);

Accepted Solutions
Solution
‎06-07-2014 03:17 PM
Contributor
Posts: 42

Re: Split up a table into multiple tables faster

You're going to love this...:smileymischief:

options symbolgen;

%let m = 240;

data ttl; do blah = 1 to 100000; do m = 1 to 240; output; end; end; run;

data line1; do code = 'data '; output; end; run;

data line2; do code = '; set ttl;'; output; end; run;

data line3; do code = 'run;'; output; end; run;

data var(drop=total m); do total = 'total_'; do m = 1 to &m.;

code = total || strip(put(m,3.));

output; end; end;

run;

data var1(keep=code1 rename=(code1=code));
set var;

if _n_ ne 1 then code1 = 'else if m = ' || strip(put(_n_,3.)) || ' then output ' || strip(code) || ';';
if _n_ = 1 then code1 = 'if m = ' || strip(put(_n_,3.)) || ' then output ' || strip(code) || ';';

run;

data code; format code \$50.; set line1 var line2 var1 line3; run;

data _null_; set code;
file '/pm/ma/scott/code.sas';
put code;
run;

%include '/pm/ma/scott/code.sas';

All Replies
Super User
Posts: 23,747

Re: Split up a table into multiple tables faster

It's generally not recommended, why do you want to do this?

It's also a very well asked question so searching on here will result in several good answers.

Super User
Posts: 10,784

Re: Split up a table into multiple tables faster

Here is :

```data have;
infile datalines expandtabs;
input Manager : \$20. Department : \$20. Sales ;
datalines;
Alomar Canned 420
Alomar Meat/Dairy 190
Alomar Paper 90
Alomar Produce 86
Andrews Canned 420
Andrews Meat/Dairy 300
Andrews Paper 200
Andrews Produce 125
Brown Canned 230
Brown Meat/Dairy 250
Brown Paper 45
Brown Produce 73
Jones Canned 220
Jones Meat/Dairy 300
Jones Paper 40
Jones Produce 70
Pelfrey Canned 420
Pelfrey Meat/Dairy 205
Pelfrey Paper 45
Pelfrey Produce 76
Reveiz Canned 420
Reveiz Meat/Dairy 600
Reveiz Paper 60
Reveiz Produce 30
Smith Canned 120
Smith Meat/Dairy 100
Smith Paper 50
Smith Produce 80
Taylor Canned 120
Taylor Meat/Dairy 130
Taylor Paper 53
Taylor Produce 50
;
run;
/* First method */
data _null_;
if _n_ eq 1 then do;
if 0 then set have;
declare hash ha(multidata:'y');
ha.definekey('manager');
ha.definedata('manager','department','sales');
ha.definedone();
end;
do until(last.manager);
set have;
by manager;
end;
ha.output(dataset: manager);
ha.clear();
run;

/* Second method */
proc sql noprint;
select distinct manager into : dsn separated by ' ' from have;
select distinct 'when("'||strip(manager)||'") output '||strip(manager) into : sel  separated by ';' from have;
quit;
data &dsn;
set have;
select(manager);
&sel ;
otherwise;
end;
run;

```

Xia Keshan

Contributor
Posts: 42

Re: Split up a table into multiple tables faster

I used a data step and do output to create the code I wanted. Then I pasted the code in. Took run time from 0.68 minutes to 2 seconds!

Not really a very pretty way of doing it, but who cares.

Old Way

data total; do id = 1 to 1000000; do m = 1 to 240; output; end; end; run;

%macro total (i,ct);

%do i = &i. %to &ct.;

proc sql;
create table total_&i. as
select *
from total_&book_year.
where m = &i.;
quit;

%end;

%mend total;

data starttime;
curtime=put(time(), time.);
call symput('starttime',curtime);
run;

%let m =240;
%total(1,&m.);

data endtime;
curtime=put(time(), time.);
call symput('endtime',curtime);
run;

%let runtime = round(((intck('second',"&starttime."t,"&endtime."t)) /60),.01);

data _null_;
call symput('runtime',&runtime.);
run;

%put &runtime.;

SYMBOLGEN:  Macro variable RUNTIME resolves to         0.68

New Way

NOTE: The data set WORK.TOTAL_239 has 4475 observations and 68 variables.
NOTE: The data set WORK.TOTAL_240 has 4475 observations and 68 variables.
NOTE: DATA statement used (Total process time):
real time           2.10 seconds
cpu time            2.06 seconds

data total_text; do m = 1 to &m.; output; end; run;

data total_text; set total_text;

text1 = 'else if m = '|| strip(put(m,3.)) || '';

text2 = ' then output total_' || strip(put(m,3.)) ||';';

text = text1 || text2;

tbl = 'total_' || strip(put(m,3.));

run;

data total_1
total_2
total_3
total_4
total_5
total_6
total_7
total_8
total_9
total_10
total_11
total_12
total_13
total_14
total_15
total_16
total_17
total_18
total_19
total_20
total_21
total_22
total_23
total_24
total_25
total_26
total_27
total_28
total_29
total_30
total_31
total_32
total_33
total_34
total_35
total_36
total_37
total_38
total_39
total_40
total_41
total_42
total_43
total_44
total_45
total_46
total_47
total_48
total_49
total_50
total_51
total_52
total_53
total_54
total_55
total_56
total_57
total_58
total_59
total_60
total_61
total_62
total_63
total_64
total_65
total_66
total_67
total_68
total_69
total_70
total_71
total_72
total_73
total_74
total_75
total_76
total_77
total_78
total_79
total_80
total_81
total_82
total_83
total_84
total_85
total_86
total_87
total_88
total_89
total_90
total_91
total_92
total_93
total_94
total_95
total_96
total_97
total_98
total_99
total_100
total_101
total_102
total_103
total_104
total_105
total_106
total_107
total_108
total_109
total_110
total_111
total_112
total_113
total_114
total_115
total_116
total_117
total_118
total_119
total_120
total_121
total_122
total_123
total_124
total_125
total_126
total_127
total_128
total_129
total_130
total_131
total_132
total_133
total_134
total_135
total_136
total_137
total_138
total_139
total_140
total_141
total_142
total_143
total_144
total_145
total_146
total_147
total_148
total_149
total_150
total_151
total_152
total_153
total_154
total_155
total_156
total_157
total_158
total_159
total_160
total_161
total_162
total_163
total_164
total_165
total_166
total_167
total_168
total_169
total_170
total_171
total_172
total_173
total_174
total_175
total_176
total_177
total_178
total_179
total_180
total_181
total_182
total_183
total_184
total_185
total_186
total_187
total_188
total_189
total_190
total_191
total_192
total_193
total_194
total_195
total_196
total_197
total_198
total_199
total_200
total_201
total_202
total_203
total_204
total_205
total_206
total_207
total_208
total_209
total_210
total_211
total_212
total_213
total_214
total_215
total_216
total_217
total_218
total_219
total_220
total_221
total_222
total_223
total_224
total_225
total_226
total_227
total_228
total_229
total_230
total_231
total_232
total_233
total_234
total_235
total_236
total_237
total_238
total_239
total_240
;

set total_&book_year.;

if m = 1    then output total_1;
else if m = 2    then output total_2;
else if m = 3    then output total_3;
else if m = 4    then output total_4;
else if m = 5    then output total_5;
else if m = 6    then output total_6;
else if m = 7    then output total_7;
else if m = 8    then output total_8;
else if m = 9    then output total_9;
else if m = 10   then output total_10;
else if m = 11   then output total_11;
else if m = 12   then output total_12;
else if m = 13   then output total_13;
else if m = 14   then output total_14;
else if m = 15   then output total_15;
else if m = 16   then output total_16;
else if m = 17   then output total_17;
else if m = 18   then output total_18;
else if m = 19   then output total_19;
else if m = 20   then output total_20;
else if m = 21   then output total_21;
else if m = 22   then output total_22;
else if m = 23   then output total_23;
else if m = 24   then output total_24;
else if m = 25   then output total_25;
else if m = 26   then output total_26;
else if m = 27   then output total_27;
else if m = 28   then output total_28;
else if m = 29   then output total_29;
else if m = 30   then output total_30;
else if m = 31   then output total_31;
else if m = 32   then output total_32;
else if m = 33   then output total_33;
else if m = 34   then output total_34;
else if m = 35   then output total_35;
else if m = 36   then output total_36;
else if m = 37   then output total_37;
else if m = 38   then output total_38;
else if m = 39   then output total_39;
else if m = 40   then output total_40;
else if m = 41   then output total_41;
else if m = 42   then output total_42;
else if m = 43   then output total_43;
else if m = 44   then output total_44;
else if m = 45   then output total_45;
else if m = 46   then output total_46;
else if m = 47   then output total_47;
else if m = 48   then output total_48;
else if m = 49   then output total_49;
else if m = 50   then output total_50;
else if m = 51   then output total_51;
else if m = 52   then output total_52;
else if m = 53   then output total_53;
else if m = 54   then output total_54;
else if m = 55   then output total_55;
else if m = 56   then output total_56;
else if m = 57   then output total_57;
else if m = 58   then output total_58;
else if m = 59   then output total_59;
else if m = 60   then output total_60;
else if m = 61   then output total_61;
else if m = 62   then output total_62;
else if m = 63   then output total_63;
else if m = 64   then output total_64;
else if m = 65   then output total_65;
else if m = 66   then output total_66;
else if m = 67   then output total_67;
else if m = 68   then output total_68;
else if m = 69   then output total_69;
else if m = 70   then output total_70;
else if m = 71   then output total_71;
else if m = 72   then output total_72;
else if m = 73   then output total_73;
else if m = 74   then output total_74;
else if m = 75   then output total_75;
else if m = 76   then output total_76;
else if m = 77   then output total_77;
else if m = 78   then output total_78;
else if m = 79   then output total_79;
else if m = 80   then output total_80;
else if m = 81   then output total_81;
else if m = 82   then output total_82;
else if m = 83   then output total_83;
else if m = 84   then output total_84;
else if m = 85   then output total_85;
else if m = 86   then output total_86;
else if m = 87   then output total_87;
else if m = 88   then output total_88;
else if m = 89   then output total_89;
else if m = 90   then output total_90;
else if m = 91   then output total_91;
else if m = 92   then output total_92;
else if m = 93   then output total_93;
else if m = 94   then output total_94;
else if m = 95   then output total_95;
else if m = 96   then output total_96;
else if m = 97   then output total_97;
else if m = 98   then output total_98;
else if m = 99   then output total_99;
else if m = 100  then output total_100;
else if m = 101  then output total_101;
else if m = 102  then output total_102;
else if m = 103  then output total_103;
else if m = 104  then output total_104;
else if m = 105  then output total_105;
else if m = 106  then output total_106;
else if m = 107  then output total_107;
else if m = 108  then output total_108;
else if m = 109  then output total_109;
else if m = 110  then output total_110;
else if m = 111  then output total_111;
else if m = 112  then output total_112;
else if m = 113  then output total_113;
else if m = 114  then output total_114;
else if m = 115  then output total_115;
else if m = 116  then output total_116;
else if m = 117  then output total_117;
else if m = 118  then output total_118;
else if m = 119  then output total_119;
else if m = 120  then output total_120;
else if m = 121  then output total_121;
else if m = 122  then output total_122;
else if m = 123  then output total_123;
else if m = 124  then output total_124;
else if m = 125  then output total_125;
else if m = 126  then output total_126;
else if m = 127  then output total_127;
else if m = 128  then output total_128;
else if m = 129  then output total_129;
else if m = 130  then output total_130;
else if m = 131  then output total_131;
else if m = 132  then output total_132;
else if m = 133  then output total_133;
else if m = 134  then output total_134;
else if m = 135  then output total_135;
else if m = 136  then output total_136;
else if m = 137  then output total_137;
else if m = 138  then output total_138;
else if m = 139  then output total_139;
else if m = 140  then output total_140;
else if m = 141  then output total_141;
else if m = 142  then output total_142;
else if m = 143  then output total_143;
else if m = 144  then output total_144;
else if m = 145  then output total_145;
else if m = 146  then output total_146;
else if m = 147  then output total_147;
else if m = 148  then output total_148;
else if m = 149  then output total_149;
else if m = 150  then output total_150;
else if m = 151  then output total_151;
else if m = 152  then output total_152;
else if m = 153  then output total_153;
else if m = 154  then output total_154;
else if m = 155  then output total_155;
else if m = 156  then output total_156;
else if m = 157  then output total_157;
else if m = 158  then output total_158;
else if m = 159  then output total_159;
else if m = 160  then output total_160;
else if m = 161  then output total_161;
else if m = 162  then output total_162;
else if m = 163  then output total_163;
else if m = 164  then output total_164;
else if m = 165  then output total_165;
else if m = 166  then output total_166;
else if m = 167  then output total_167;
else if m = 168  then output total_168;
else if m = 169  then output total_169;
else if m = 170  then output total_170;
else if m = 171  then output total_171;
else if m = 172  then output total_172;
else if m = 173  then output total_173;
else if m = 174  then output total_174;
else if m = 175  then output total_175;
else if m = 176  then output total_176;
else if m = 177  then output total_177;
else if m = 178  then output total_178;
else if m = 179  then output total_179;
else if m = 180  then output total_180;
else if m = 181  then output total_181;
else if m = 182  then output total_182;
else if m = 183  then output total_183;
else if m = 184  then output total_184;
else if m = 185  then output total_185;
else if m = 186  then output total_186;
else if m = 187  then output total_187;
else if m = 188  then output total_188;
else if m = 189  then output total_189;
else if m = 190  then output total_190;
else if m = 191  then output total_191;
else if m = 192  then output total_192;
else if m = 193  then output total_193;
else if m = 194  then output total_194;
else if m = 195  then output total_195;
else if m = 196  then output total_196;
else if m = 197  then output total_197;
else if m = 198  then output total_198;
else if m = 199  then output total_199;
else if m = 200  then output total_200;
else if m = 201  then output total_201;
else if m = 202  then output total_202;
else if m = 203  then output total_203;
else if m = 204  then output total_204;
else if m = 205  then output total_205;
else if m = 206  then output total_206;
else if m = 207  then output total_207;
else if m = 208  then output total_208;
else if m = 209  then output total_209;
else if m = 210  then output total_210;
else if m = 211  then output total_211;
else if m = 212  then output total_212;
else if m = 213  then output total_213;
else if m = 214  then output total_214;
else if m = 215  then output total_215;
else if m = 216  then output total_216;
else if m = 217  then output total_217;
else if m = 218  then output total_218;
else if m = 219  then output total_219;
else if m = 220  then output total_220;
else if m = 221  then output total_221;
else if m = 222  then output total_222;
else if m = 223  then output total_223;
else if m = 224  then output total_224;
else if m = 225  then output total_225;
else if m = 226  then output total_226;
else if m = 227  then output total_227;
else if m = 228  then output total_228;
else if m = 229  then output total_229;
else if m = 230  then output total_230;
else if m = 231  then output total_231;
else if m = 232  then output total_232;
else if m = 233  then output total_233;
else if m = 234  then output total_234;
else if m = 235  then output total_235;
else if m = 236  then output total_236;
else if m = 237  then output total_237;
else if m = 238  then output total_238;
else if m = 239  then output total_239;
else if m = 240  then output total_240;
run;

Super User
Posts: 23,747

Re: Split up a table into multiple tables faster

```econ wrote:

Not really a very pretty way of doing it, but who cares.

```

The future you who has to change the code, or the person after you who has to fix something.  If its for a homework assignment and will never ever be touched again I suppose you're good.

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

If you look at the very bottom of the that page it also has 3 other links on how to do it several ways in a two step solution.

Posts: 3,167

Re: Split up a table into multiple tables faster

Reeza, here is one of my favorite  quotes

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live”

― John Woods

Super User
Posts: 13,556

Re: Split up a table into multiple tables faster

Having inherited some very "interesting code" I'm afraid I admit to wanting to meet some coders in a dark alley with a baseball bat ...

Posts: 3,167

Re: Split up a table into multiple tables faster

LOL. Me, two. You don't have to be Psychopath per se, some code will make it happen.

Contributor
Posts: 42

Re: Split up a table into multiple tables faster

Good thinking. I'll make sure to bury this deep in some obscure macro

When I have more time I think about it a little more.

Solution
‎06-07-2014 03:17 PM
Contributor
Posts: 42

Re: Split up a table into multiple tables faster

You're going to love this...:smileymischief:

options symbolgen;

%let m = 240;

data ttl; do blah = 1 to 100000; do m = 1 to 240; output; end; end; run;

data line1; do code = 'data '; output; end; run;

data line2; do code = '; set ttl;'; output; end; run;

data line3; do code = 'run;'; output; end; run;

data var(drop=total m); do total = 'total_'; do m = 1 to &m.;

code = total || strip(put(m,3.));

output; end; end;

run;

data var1(keep=code1 rename=(code1=code));
set var;

if _n_ ne 1 then code1 = 'else if m = ' || strip(put(_n_,3.)) || ' then output ' || strip(code) || ';';
if _n_ = 1 then code1 = 'if m = ' || strip(put(_n_,3.)) || ' then output ' || strip(code) || ';';

run;

data code; format code \$50.; set line1 var line2 var1 line3; run;

data _null_; set code;
file '/pm/ma/scott/code.sas';
put code;
run;

%include '/pm/ma/scott/code.sas';

Super User
Posts: 10,259

Re: Split up a table into multiple tables faster

%macro splittable(tablein,tableout,var,start,end);

data

%do i = &start %to &end;

&tableout&i

%end;

;

set &tablein;

select (&var);

%do i = &start %to &end;

when(&i) output &tableout&i;

%end;

end;

%mend;

%splittable(have,want,m,1,240);

Works at least for all &var which are numeric.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
🔒 This topic is solved and locked.