topic difference between two data sets - help required in SAS Programming

difference between two data sets - help required

SASUserRocks — Mon, 19 Jun 2023 13:27:17 GMT

Need help in below code for weekly representation of data using sas proc sql.

Expired Insurances	12-Jun-23	19-Jun-23
Team	Support Manager	Total Cases	Cases Resolved	% of Resolved	Cases Added	Cases Pending as of today
AAAA	AB	21	3	14%	7	25
BBBB	BC	11	2	18%	0	9

Here I have raw data for 12june and 19 june. Based on docid i have find total cases , cases resolved and cased added details as above.

wrote separte queries to get current week and past week data as below. Need help to join two data sets and get all above info in one table.

proc sql;
create table Current_Week_Cases
as select distinct A.Current_week as Current_week,
A.'Support Manager'n,
A.Team,
A.'Document Type'n,
A.'Doc register ID'n,
count(A.'Doc register ID'n) as 'Total Cases'n
from (select Current_week,Team,'Support Manager'n,'Doc register ID'n,'Document Type'n from WBG.Exception_Report where Current_week="&StartWeek."d and
'Document Type'n='Insurance' and 'Document Status'n='Expired') as A
group by A.Current_week,A.Team;
run;

proc sql;
create table Previous_Week_Cases
as select distinct A.Current_week as Current_week,
A.'Support Manager'n,
A.Team,A.'Document Type'n,
A.'Doc register ID'n,
count(A.'Doc register ID'n) as 'Total Cases'n
from (select Current_week,Team,'Support Manager'n,'Doc register ID'n,'Document Type'n from WBG.Exception_Report where Current_week="&PrevWeek."d and
'Document Type'n='Insurance' and 'Document Status'n='Expired') as A
group by A.Current_week,A.Team;
run;

Logic :-

Total cases :- count of doc id for previous week

cases resolved :- doc ids in past week but not present in current week

cases added : doc ids in current week but not in past week

Re: difference between two data sets - help required

PaigeMiller — Mon, 19 Jun 2023 13:58:36 GMT

Can you please be kind enough to show us a portion of this data before you performed this SQL on it? Can you please be kind enough to show us the desired output?

Re: difference between two data sets - help required

SASUserRocks — Mon, 19 Jun 2023 14:10:54 GMT

Desired output is given in grid

Re: difference between two data sets - help required

PaigeMiller — Mon, 19 Jun 2023 14:13:55 GMT

Repeating

Can you please be kind enough to show us a portion of this data before you performed this SQL on it? Can you please be kind enough to show us the desired output?

Also, is the desired output a SAS data set, or a report that you can print or include in a document?

Re: difference between two data sets - help required

SASUserRocks — Mon, 19 Jun 2023 14:42:16 GMT

Here is sample portion of data :- Output needs to be in data set.

Current_week RM Name PC Code PC Name CID Customer Code Team Support Manager Doc register ID Client Name Description / Remarks Document Class Document Type Document Name Document Date Document Status Expiry Date Ageing (Days) Query Amount
12-Jun-23 Rajesh Kohly 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 1205790 Security / Collateral Mortgage Supporting Docs Valuation Report 12MAY22:00:00:00 Expired 11/5/2023 39 More than 15 Days .
12-Jun-23 Riyaz Hibtullah Attari 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 1028883 Customer Source Docs Trade Licence Copy 24MAY22:00:00:00 Expired 3/6/2023 16 More than 15 Days .
12-Jun-23 Ali Ahmed 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 1145878 Security / Collateral Shares Share Pledge 23MAR22:00:00:00 Condition Subsequent (Post-facto) EDR-Lapsed 23/06/2022 361 More than 15 Days .
12-Jun-23 Ali Ahmed 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 860375 Charge Document Ancillary Docs Misc. Documents 21MAR22:00:00:00 Expired 20/03/2023 91 More than 15 Days .
12-Jun-23 Mohammed Nihad Al Sare Khea 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 119406 Customer Source Docs Trade Licence Copy 05OCT21:00:00:00 Deferral Expired 31/05/2023 19 More than 15 Days .
12-Jun-23 Riyaz Hibtullah Attari 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 697052 Customer Source Docs Trade Licence Copy 03JUN13:00:00:00 Expired 2/6/2023 17 More than 15 Days .
12-Jun-23 Mohammed Nihad Al Sare Khea 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 1205728 Security / Collateral Mortgage Supporting Docs Valuation Report 25MAY21:00:00:00 Expired 24/05/2023 26 More than 15 Days .
12-Jun-23 Khaled Ahmed Mohamed Hassan 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 853057 Charge Document Charge Docs Promissory Note 30MAY22:00:00:00 Expired 29/05/2023 21 More than 15 Days .
12-Jun-23 Ali Ahmed 290 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 1157720 Syndication and External Documents Syndication & Externally Prepared Docs Others 25MAR22:00:00:00 Condition Subsequent (Post-facto) EDR-Lapsed 1/4/2022 444 More than 15 Days .
12-Jun-23 Ali Ahmed 622 AAN Corporate XXX XXX AUH CORP Mazen Al Masoud 854274 Charge Document Charge Docs Promissory Note 06MAY20:00:00:00 Expired 5/5/2023 45 More than 15 Days .
19-Jun-23 Ahmed Amer 288 AAN Government Clients XXX XXX AUH GOV Hassan Darwish 1129920 Security / Collateral Shares Share Pledge 24NOV20:00:00:00 Condition Subsequent (Post-facto) EDR-Lapsed 11/11/2020 950 More than 15 Days .
19-Jun-23 Omar Mustafa 288 AAN Government Clients XXX XXX AUH GOV Hassan Darwish 1190672 Agreements Conventional Amendment of Credit Facility Agreement . Condition Subsequent (Post-facto) EDR-Lapsed 27/07/2022 327 More than 15 Days .
19-Jun-23 Ahmed Amer 501 AAN Government Clients XXX XXX AUH GOV Hassan Darwish 956262 Syndication and External Documents Syndication & Externally Prepared Docs Others . Defective (Incomplete) 16/04/2021 794 More than 15 Days .
19-Jun-23 Ahmed Amer 501 AAN Government Clients XXX XXX AUH GOV Hassan Darwish 837015 Customer Source Docs Trade Licence Copy 17JAN22:00:00:00 Expired 16/01/2023 154 More than 15 Days .
19-Jun-23 Ahmed Amer 288 AAN Government Clients XXX XXX AUH GOV Hassan Darwish 862777 Syndication and External Documents Syndication & Externally Prepared Docs Others 14OCT20:00:00:00 Condition Subsequent (Post-facto) EDR-Lapsed 15/12/2020 916 More than 15 Days .
19-Jun-23 Mona Abuobaida 288 AAN Government Clients XXX XXX AUH GOV Hassan Darwish 1353526 Charge Document Charge Docs Appointment of Service Agent 03APR23:00:00:00 Defective (Incomplete) 7/6/2023 12 .
19-Jun-23 Ahmed Amer 288 AAN Government Clients XXX XXX AUH GOV Hassan Darwish 958688 Customer Source Docs Trade Licence Copy 18MAY22:00:00:00 Expired 4/6/2023 15 More than 15 Days .

Re: difference between two data sets - help required

PaigeMiller — Mon, 19 Jun 2023 14:45:35 GMT

My mistake ... I forgot to mention that data must be in the form of WORKING SAS data step code. Here are examples and instructions. I can't work with what you have provided.