Hello,
Here is the solution
data table1;
input CART $ INI_3 MED_1 FIN_2;
datalines;
A 1.50 2.90 4.30
B 1.30 2.40 4.80
;
run;
data table2;
input REF INI MED FIN;
datalines;
1 1.44 9.50 5.76
2 3.70 7.80 6.80
3 5.96 6.10 7.84
4 8.22 4.40 8.88
5 10.48 2.70 9.92
6 12.74 1.00 10.96
7 4.70 6.98 11.00
8 17.26 12.96 11.04
9 29.82 18.94 11.08
10 42.38 24.92 11.12
;
run;
proc sql;
create table test as
select a.*,b.* from table1 as a , table2 as b;
quit;
options minoperator mindelimiter=',';
%macro calculation(outdataset=,ref_tab1=,ref_i_3=,ref_m_1=,ref_f_2=,finaldata=);
proc sql;
create table &outdataset as
select a.cart,a.ini_3,a.FIN_2,a.MED_1,b.FIN,b.MED,b.ini,b.ref as ref_b,a.ref from
test(where=(ref eq &ref_tab1)) as a , test (where=(ref in (&ref_i_3.,&ref_m_1.,&ref_f_2.))) as b where a.cart=b.cart ;
quit;
proc sql;
create table &finaldata as
select cart,sum(output1) as output from
(select cart,sum(ind1,med1,fin2) as output1 from (
select cart,
case when ref eq &ref_tab1. and ref_b eq &ref_i_3. then ini_3*ini else . end as ind1,
case when ref eq &ref_tab1. and ref_b eq &ref_m_1. then MED_1*MED else . end as MED1,
case when ref eq &ref_tab1. and ref_b eq &ref_f_2. then FIN_2*FIN else . end as fin2 from &outdataset)group by cart)group by cart;
quit;
%mend;
%calculation(outdataset=tab1,ref_tab1=1,ref_i_3=4,ref_m_1=2,ref_f_2=3,finaldata=_want1);
%calculation(outdataset=tab1,ref_tab1=2,ref_i_3=5,ref_m_1=3,ref_f_2=4,finaldata=_want2);
%calculation(outdataset=tab1,ref_tab1=3,ref_i_3=6,ref_m_1=4,ref_f_2=5,finaldata=_want3);
%calculation(outdataset=tab1,ref_tab1=4,ref_i_3=7,ref_m_1=5,ref_f_2=6,finaldata=_want4);
%calculation(outdataset=tab1,ref_tab1=5,ref_i_3=8,ref_m_1=6,ref_f_2=7,finaldata=_want5);
%calculation(outdataset=tab1,ref_tab1=6,ref_i_3=9,ref_m_1=7,ref_f_2=8,finaldata=_want6);
%calculation(outdataset=tab1,ref_tab1=6,ref_i_3=10,ref_m_1=8,ref_f_2=9,finaldata=_want7);
PROC SQL;
SELECT MEMNAME INTO :DATASETNAME SEPARATED BY ' ' FROM DICTIONARY.TABLES WHERE LIBNAME='WORK' AND MEMNAME LIKE '_W%';
QUIT;
DATA FINAL;
SET &DATASETNAME;
RUN;
PROC SORT;BY CART;RUN;
Thanks & Regards:
🙂