May I answer your part2 first, as you may not need part1 to get part2.
This is your provided data file (Thank you for the ready-to-go input data):
data testdata;
infile datalines dsd truncover;
input
ordernum:$3.
order:1.
code:$3.
;
datalines;
111, 1, qwe
111, 2, gfd
111, 3, sdf
222, 1, erw
222, 2, fgh
333, 1, saa
333, 2, ath
333, 3, gdw
444, 1, etg
444, 2, hef
444, 3, sfy
444, 4, hrl
555, 1, mmj
555, 2, bhg
555, 3, dgy
555, 4, ggd
555, 5, dss
;;;
Now this to answer the your part2:
/*transpose to get all needed elements*/
PROC SORT
DATA=WORK.TESTDATA(KEEP=code order ordernum)
OUT=WORK.Sorted
;
BY ordernum;
RUN;
PROC TRANSPOSE DATA=WORK.Sorted
OUT=WORK.TRNS(drop=_name_)
PREFIX=code
;
BY ordernum;
ID order;
VAR code;
RUN;
QUIT;
/*merge ONE table*/
proc sql;
create table New_Table as
select
*
from master a
left join WORK.TRNS b on a.ordernum = b.ordernum
;
quit;
If for some reason splitting is still needed, for part1, here is one dynamic non-macro splitting approach:
/*Hash split*/
PROC SORT
DATA=WORK.TESTDATA(KEEP=code order ordernum)
OUT=WORK.Hash_sorted
;
BY order;
RUN;
data _null_;
if _n_=1 then
do;
/*if 0 then set hash_sorted;*/
declare hash h(dataset:'hash_sorted', multidata:'y');
h.definekey('order');
h.definedata(all:
'y');
h.definedone();
end;
rc=h.clear();
do until (last.order);
set hash_sorted;
by order;
rc=h.add();
end;
rc=h.output(dataset:cats('ordernum',order));
run;
... View more
