Variation in sql

WilliamB — Thu, 25 Oct 2018 08:45:30 GMT

Hello,

I have 2 tables, the first in T and the second in T-1.
I would like to gather these 2 tables, and calculate the variation rates of variation P and C.

Table T:

Id	lib	P_t	C_t
8157	ABU DHABI INVESTMENT AUTHORITY	65901635,61	1323542,3
21504	ROYAUME DES PAYS BAS - KINGDOM OF THE NETHERLANDS	148436781,6	0
22484	REPUBLIQUE DE COTE D'IVOIRE	11713154,54	8452866,03
92137	REPUBLIQUE POPULAIRE DE CHINE	29409445,09	0
93043	UNITED KINGDOM OF GREAT BRITAIN AND NORTHERN IRELAND	1452	0
97827	GOUVERNEMENT DU JAPON - JAPAN	1516	303,2

Table T-1

Id	lib	P_t1	C_t1
8157	ABU DHABI INVESTMENT AUTHORITY	73594883,7	1478050,48
21504	ROYAUME DES PAYS BAS - KINGDOM OF THE NETHERLANDS	135298880,5	0
83590	THE COMMONWEALTH OF AUSTRALIA AUSTRALIA	20290	0
89761	CANADA GOUVERNEMENT	361	0
93043	UNITED KINGDOM OF GREAT BRITAIN AND NORTHERN IRELAND	4351	0
103335	REPUBLIQUE DE LITHUANIE	51975000	1104323,66

Here's what I want:

Id	lib	P_t	C_t	P_t_1	C_t_1	Variation P	Variation C
8157	ABU DHABI INVESTMENT AUTHORITY	65901635,61	1323542,3	73594883,7	1478050,48	-7693248,1	-154508,18
21504	ROYAUME DES PAYS BAS - KINGDOM OF THE NETHERLANDS	148436781,6	0	135298880,5	0	13137901,2	0
22484	REPUBLIQUE DE COTE D'IVOIRE	11713154,54	8452866,03	0	0	11713154,5	8452866,03
92137	REPUBLIQUE POPULAIRE DE CHINE	29409445,09	0	0	0	29409445,1	0
93043	UNITED KINGDOM OF GREAT BRITAIN AND NORTHERN IRELAND	1452	0	4351	0	-2899	0
97827	GOUVERNEMENT DU JAPON - JAPAN	1516	303,2	0	0	1516	303,2
83590	THE COMMONWEALTH OF AUSTRALIA AUSTRALIA	0	0	20290	0	-20290	0
89761	CANADA GOUVERNEMENT	0	0	361	0	-361	0
103335	REPUBLIQUE DE LITHUANIE	0	0	51975000	1104323,66	-51975000	-1104323,7

Thanks a lot for your help .

Re: Variation in sql

RW9 — Thu, 25 Oct 2018 08:52:08 GMT

Post test data in the form of a datastep. As such this is just an example:

proc sql;
  select  a.*,
          b.*,
          b.p_t1-a.p_t as variationp,
          b.c_t1-a.c_t as variationc
  from    tablet a
  full join tablet1 b
  in      a.id=b.id
  and     a.lib=b.lib;
quit;

Re: Variation in sql

SuryaKiran — Thu, 25 Oct 2018 13:59:10 GMT

You need to be more specific on the values you have. Your values are like -->65901635,61 is this a single values or two values separated by comma? If they were two values then what was your logic for calculating -7693248,1 How did you get that one there?

Please be more specific and provide your sample data in the form of a data step.

topic Variation in sql in New SAS User

Variation in sql

Re: Variation in sql

Re: Variation in sql