DATA Transforming from SQL TO SAS

harrylui — Tue, 06 Nov 2018 03:48:15 GMT

hi all!

i am struck in DATA Transforming from SQL TO SAS

the answers given by SAS AND SQL don't match

can anyone do me a favour?

this is my SQL coding

create table ClaimMedLL as
SELECT
Claim.Period
,Claim.UWYear
,Claim.EventPeriod
,SUM(Claim.NTD_Incurred_Loss_Excl_Reval_Ledger) as MedL
,SUM(Claim.NTD_Incurred_Loss_Excl_Reval_USD) as MedL_USD
FROM Claim
WHERE ClaimNo IN (
SELECT ClaimNo FROM Claim GROUP BY ClaimNo HAVING SUM(Claim.NTD_Incurred_Loss_Excl_Reval_USD) > 50000
)
AND Claim.Pac IN ('GMMAN')
GROUP BY Period, UWYear, EventPeriod

i just simply add the proc sql and run into this coding

here is my screen cap of my portion of data set claim

Obs	Period	UWYear	EventPeriod	Pac	Product	PolicyNo	ClaimNo	ClaimantNo	CoverageCode	NTDIndemnityLossPaidUSD	NTDClaimExpensePaidUSD	NTDReserveChangeExclRevalUSD	NTDIncurredLossExclRevalUSD	NTDIndemnityLossPaidLedger	NTDClaim_ExpensePaidLedger	NTDReserveChangeExclRevalLedger	NTDIncurredLossExclRevalLedger
1	201801	2000	200007	FAAMC	370204	HID0951913	4.05E+09	1	PPFR	0	0	0	0	0	0	0	0
2	201801	2000	201501		371901	KCANC59586	4.04E+09	1		96075.75	0	-96075.8	.	750000	0	-750000	.
3	201801	2000	201702		371901	MOP005210C	4.04E+09	1		0	139.89	0	.	0	1092	0	.
4	201801	2000	201703		371901	0442890CCG	4.04E+09	1		1694	365.47	-4	.	13223.94	2853	-31.23	.
5	201801	2000	201706		371901	HCGNA00133	4.04E+09	1		0	139.89	0	.	0	1092	0	.
6	201801	2000	201706		371901	PGC0444724	4.04E+09	1		0	267.99	0	.	0	2092	0	.
7	201801	2000	201707		371901	87TB4673	4.04E+09	1		0	99.92	-4	.	0	780	-31.23	.
8	201801	2000	201707		371901	HCGNA00133	4.04E+09	1		0	267.99	0	.	0	2092	0	.
9	201801	2000	201707		371901	M-1236	4.04E+09	1		0	1797.47	0	.	0	14031.67	0	.
10	201801	2000	201708		371901	2MC-800035	4.04E+09	1		510.61	0	-512.4	.	3986.03	0	-4000	.
11	201801	2000	201708		371901	M-1021	4.04E+09	1		1944.44	0	-20000	.	15178.96	0	-156127	.
12	201801	2000	201708		371901	M-1021	4.04E+09	1		0	139.89	0	.	0	1092	0	.
13	201801	2000	201708		371901	M-1021	4.04E+09	1		0	139.89	0	.	0	1092	0	.
14	201801	2000	201708		371901	M-1021	4.04E+09	1		0	214.57	0	.	0	1675	0	.
15	201801	2000	201708		371901	M-1021	4.04E+09	1		0	0	0	.	0	0	0	.
16	201801	2000	201709		371901	M-1021	4.04E+09	1		0	139.89	0	.	0	1092	0	.
17	201801	2000	201709		371901	M-1021	4.04E+09	1		0	139.89	0	.	0	1092	0	.
18	201801	2000	201709		371901	M-1021	4.04E+09	1		0	139.89	0	.	0	1092	0	.

Re: DATA Transforming from SQL TO SAS

ballardw — Tue, 06 Nov 2018 15:08:56 GMT

Show the "SAS" code that doesn't match.

Note that if you mean data step code then the SUM function for a data step works on a single row, not across rows as the SQL sum aggregate function.

Typically SQL Sum by group translates to Proc Means or Summary with Group by variables becoming CLASS variables.

But you would not be able to use where statement referencing a second data set so the data would have to be reduced prior to the proc.

Re: DATA Transforming from SQL TO SAS

harrylui — Wed, 07 Nov 2018 01:38:31 GMT

proc sql;
create table ClaimMedLL as
SELECT
Period
,UWYear
,EventPeriod
,SUM(NTDIncurredLossExclRevalLedger) as MedL
,SUM(NTDIncurredLossExclRevalUSD) as MedL_USD
FROM Claim
WHERE ClaimNo IN (
SELECT Claim.ClaimNo FROM Claim GROUP BY Claim.ClaimNo HAVING SUM(Claim.NTDIncurredLossExclRevalUSD) > 50000
)
AND Claim.Pac IN ('GMMAM')
GROUP BY Period, UWYear, EventPeriod
;
run;

this is the code i am using

is it really different from the SQL processing?

topic DATA Transforming from SQL TO SAS in New SAS User

DATA Transforming from SQL TO SAS

Re: DATA Transforming from SQL TO SAS

Re: DATA Transforming from SQL TO SAS