Sorry there are some errors that were made during cleaning and Simplifying the code after testing.
Above line 2 seems having problems, it might be caused by copying and pasting the fonts from different computers.
the line 2 should be read as:
input id datatype $ ori_data $ cor_data $;
line 23 is not correct. it should be read as:
call execute('data '||ds||'; set test(where =(datatype="'||type||'")); run;');
The following is the corrected code.
- data test;
- input ID DATATYPE $ ORI_DATA $ COR_DATA $;
- datalines;
- 1 A XXX XYX
- 1 A XYX XXY
- 2 A YYY ZMZ
- 2 B TTT TRT
- 3 C YYX YYY
- 4 D ZZZ ZZX
- ; run;
- /* using call execute */
- proc sql;
- create table types as
- select distinct datatype as type
- from test;
- quit;
- data _null_;
- set types;
- length ds $8.;
- ds=cats('type_', compress(type));
- call execute('data '||ds||'; set test(where =(datatype="'||type||'")); run;');
- run;
- /* using hash */
- proc sort data=test out=test_sort;
- by datatype;
- run;
- data _null_ ;
- declare hash h (ordered: 'a') ;
- h.definekey ('datatype', '_n_') ;
- h.definedata ('DATATYPE', 'ID', 'ORI_DATA', 'COR_DATA' ) ;
- h.definedone ( ) ;
- do _n_ = 1 by 1 until ( last.datatype ) ;
- set test_sort;
- by datatype ;
- h.add() ;
- end ;
- h.output (dataset: 'Out_'|| compress(datatype)) ;
- run ;
复制代码here is the log
235 data test;
236 input ID DATATYPE $ ORI_DATA $ COR_DATA $;
237 datalines;
NOTE: The data set WORK.TEST has 6 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
244 ; run;
245
246 /* using call execute */
247 proc sql;
248 create table types as
249 select distinct datatype as type
250 from test;
NOTE: Table WORK.TYPES created, with 4 rows and 1 columns.
251 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
252
253 data _null_;
254 set types;
255 length ds $8.;
256 ds=cats('type_', compress(type));
257 call execute('data '||ds||'; set test(where =(datatype="'||type||'")); run;');
258 run;
NOTE: There were 4 observations read from the data set WORK.TYPES.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: CALL EXECUTE generated line.
1 + data type_A ; set test(where =(datatype="A ")); run;
NOTE: There were 3 observations read from the data set WORK.TEST.
WHERE datatype='A ';
NOTE: The data set WORK.TYPE_A has 3 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
2 + data type_B ; set test(where =(datatype="B ")); run;
NOTE: There were 1 observations read from the data set WORK.TEST.
WHERE datatype='B ';
NOTE: The data set WORK.TYPE_B has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
3 + data type_C ; set test(where =(datatype="C ")); run;
NOTE: There were 1 observations read from the data set WORK.TEST.
WHERE datatype='C ';
NOTE: The data set WORK.TYPE_C has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
4 + data type_D ; set test(where =(datatype="D ")); run;
NOTE: There were 1 observations read from the data set WORK.TEST.
WHERE datatype='D ';
NOTE: The data set WORK.TYPE_D has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
259
260
261 /* using hash */
262 proc sort data=test out=test_sort;
263 by datatype;
264 run;
NOTE: There were 6 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST_SORT has 6 observations and 4 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
265
266 data _null_ ;
267 declare hash h (ordered: 'a') ;
268 h.definekey ('datatype', '_n_') ;
269 h.definedata ('DATATYPE', 'ID', 'ORI_DATA', 'COR_DATA' ) ;
270 h.definedone ( ) ;
271 do _n_ = 1 by 1 until ( last.datatype ) ;
272 set test_sort;
273 by datatype ;
274 h.add() ;
275 end ;
276 h.output (dataset: 'Out_'|| compress(datatype)) ;
277 run ;
NOTE: The data set WORK.OUT_A has 3 observations and 4 variables.
NOTE: The data set WORK.OUT_B has 1 observations and 4 variables.
NOTE: The data set WORK.OUT_C has 1 observations and 4 variables.
NOTE: The data set WORK.OUT_D has 1 observations and 4 variables.
NOTE: There were 6 observations read from the data set WORK.TEST_SORT.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds