Here is another more approach
data have;
infile datalines delimiter=',' dsd;
length claim_job_id $20. inc_mtr_no $10. Qty $3.;
input claim_job_id inc_mtr_no Qty;
datalines;
7698-2720K-282-1,990950,100
7698-2720K-282-1,GRO836029,100
7698-2826K-316292-2,FV HC19350,10
7698-2826K-316292-2,FV HC19375,10
7698-2826K-317055-3,GRO833100,100
7698-2826K-317055-3,GRO833101,100
7698-2826K-317108-2,GRO833100,100
7698-2826K-317108-2,GRO833101,100
7698-2826K-319505-1,FV HC19350,10
7698-2826K-319505-1,FV HC19375,10
7698-2826K-325714-8,GRO833100,100
7698-2826K-325714-8,GRO833101,100
7698-2826K-331417-1,GRO833100,100
7698-2826K-331417-1,GRO833101,100
7698-2826K-331425-1,GRO833100,100
7698-2826K-331425-1,GRO833101,100
7698-2826K-331425-1,GRO833133,50
;
run;
/* Find the records count by the ID variable */ proc sql noprint;
select distinct count(claim_job_id) into :g_max separated by ',' from have
group by claim_job_id;
quit;
/* Get the maximum count of records */
%put &=g_max;
%let g_max=%sysfunc(max(&g_max));
%put &=g_max;
/* Declare iteration macro program for use within RETAIN Statement */
%macro iterate();
%do v=1 %to &g_max;
inc_mtr_no&v qty&v
%end;
%mend;
data want(DROP=inc_mtr_no qty i);
RETAIN claim_job_id %iterate;
ARRAY inc_mtr_nos {3} $10 inc_mtr_no1-inc_mtr_no&g_max;
ARRAY qties {3} $3 qty1-qty&g_max;
do until (last.claim_job_id);
set have;
by claim_job_id;
i+1;
inc_mtr_nos[i]=inc_mtr_no;
qties[i]=qty;
end;
i=0;
run;
Hope this helps,
Ahmed
... View more
