Split up a table into multiple tables faster

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

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

View solution in original post


All Replies
Super User
Posts: 19,878

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,046

Re: Split up a table into multiple tables faster

Here is :

data have;
infile datalines expandtabs;
input Manager : $20. Department : $20. Sales ;
datalines;
Adams Canned 225
Adams Meat/Dairy 350
Adams Paper 40
Adams Produce 80
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;
 ha.add();
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: 19,878

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.

Respected Advisor
Posts: 3,156

Re: Split up a table into multiple tables faster

Reeza, here is one of my favorite  quotes Smiley Happy

“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: 11,343

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

Respected Advisor
Posts: 3,156

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 macroSmiley Happy

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: 7,866

Re: Split up a table into multiple tables faster

How about

%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
🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 1113 views
  • 2 likes
  • 6 in conversation