topic Re: Split up a table into multiple tables faster in SAS Programming

Split up a table into multiple tables faster

econ — Thu, 24 Apr 2014 00:27:30 GMT

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

Re: Split up a table into multiple tables faster

Reeza — Thu, 24 Apr 2014 01:43:44 GMT

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.

Re: Split up a table into multiple tables faster

Ksharp — Thu, 24 Apr 2014 01:50:10 GMT

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

Re: Split up a table into multiple tables faster

econ — Thu, 24 Apr 2014 15:09:26 GMT

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;

Re: Split up a table into multiple tables faster

Reeza — Thu, 24 Apr 2014 15:37:03 GMT

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.

Re: Split up a table into multiple tables faster

Haikuo — Thu, 24 Apr 2014 15:55:58 GMT

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

Re: Split up a table into multiple tables faster

ballardw — Thu, 24 Apr 2014 16:06:33 GMT

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

Re: Split up a table into multiple tables faster

Haikuo — Thu, 24 Apr 2014 16:12:57 GMT

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

Re: Split up a table into multiple tables faster

econ — Thu, 24 Apr 2014 18:05:44 GMT

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.

Re: Split up a table into multiple tables faster

Kurt_Bremser — Fri, 25 Apr 2014 05:38:38 GMT

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.

Re: Split up a table into multiple tables faster

econ — Sat, 07 Jun 2014 19:17:46 GMT

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