join tables with "lag"

[usuario_estudo]

Hello,

Please, can anybody help me?

I need to join two tables (TABLE 1 and TABLE 20 to get TABLE 3, as I explain and exemplify it in the attached excel.) Can you help me, because I am not able to create this code.

I need to do this in SAS in sql, for a number of variables and a larger amount of values. What I send in excel is just one example.

[singhsahab]

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:

🙂