cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
Reddi
Fluorite | Level 6 Reddi
Fluorite | Level 6

Proc sql Join

Posted 03-14-2017 04:44 AM (1465 views)

Hi ,

 

I'm facing a problem while joining the two tables in the below fashion. not able to get the proper out put.

 

proc sql;

create table Windscreen_RM as

select t1.repyr,t1.repmthdt,sum(case when t1.flg_NilSettledInc="N" then t1.cnt_Settle end ) as Settled_with_pay label="Settled count with Pay",

sum(case when t1.flg_NilSettledInc="Y" then t1.cnt_Settle end ) as Settled_wO_pay label="Settled count without Pay",

sum(case when t1.rpnmth=. then t1.cnt_Outstand end) as outstand label="Outstanding Count",t2.Notified_cnt

from WORK.RM_MOD_Windscreen t1 left outer join (select mdy(month(not_dat),01,year(not_dat)) as notif_mnth format=ddmmyy10.,count(distinct cats(clm_key,not_dat)) as Notified_cnt label="Reported Claims"

from WORK.RM_MOD_Windscreen

where notyr=repyr

group by calculated notif_mnth) as t2

on t1.notyr=t1.repyr and t1.repmthdt=t2.notif_mnth

group by t1.repyr,t1.repmthdt

;

quit;

 

The individual outputs are below.

 

Individual out put of outer query Individual output of inner query
Reporting yearReporting monthsettled count with payNil settledout Notif_mnthNotified_cnt
201301/01/2013..2 01/01/20132
201301/02/20133.0 01/02/20131
201301/03/2013..6 01/03/20137
201301/04/20132.4 01/06/20133
201301/05/20131.4 01/07/20131
201301/06/20131.5 01/12/20131
201301/07/2013..6 01/05/20142
201301/08/2013313 01/08/20141
201301/09/2013.12 01/02/20152
201301/10/2013..2 01/04/20153
201301/11/2013..2 01/06/20151
201301/12/2013..3 01/09/20152
201401/05/2014..1 01/11/20151
201401/06/2014..1 01/02/20161
201401/07/2014..1 01/03/20162
201401/08/2014..1 01/04/20163
201401/09/2014..1 01/07/20161
201401/10/2014..1 01/11/201619
201401/11/20141.1 01/12/201622
201401/12/2014..1 01/01/201726
201501/02/2015..1 01/02/201723
201501/03/2015.10   
201501/04/20151.0   
201501/06/20151..   
201501/07/2015..1   
201501/09/2015..1   
201501/10/20154.0   
201501/11/2015..1   
201501/12/2015..1   
201601/02/20161.0   
201601/03/2016..1   
201601/04/2016211   
201601/05/2016..1   
201601/06/2016..1   
201601/07/20161.1   
201601/08/2016..1   
201601/09/2016..1   
201601/10/20161.1   
201601/11/20166.13   
201601/12/20167.31   
201701/01/20176.19   
201701/02/201719130   

 

Can someone tweak the code if i went somewhere wrong?

0 Likes
Reply
6 REPLIES 6
Reddi
Fluorite | Level 6 Reddi
Fluorite | Level 6

Re: Proc sql Join

Posted 03-14-2017 04:46 AM (1464 views)  |   In reply to Reddi

Outputs are not aligned properly.

 

the last two columns are output of inner query.

0 Likes
Reply
Kurt_Bremser
Super User Kurt_Bremser
Super User

Re: Proc sql Join

Posted 03-14-2017 05:03 AM (1451 views)  |   In reply to Reddi

Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to create a datastep of a usable subset of your input data and post it here, and also post the expected result for that subset, so we have something to play around with.

And do some visual formatting of your code, like

proc sql;
create table Windscreen_RM as
select
  t1.repyr,
  t1.repmthdt,
  sum(case when t1.flg_NilSettledInc="N" then t1.cnt_Settle end ) as Settled_with_pay label="Settled count with Pay",
  sum(case when t1.flg_NilSettledInc="Y" then t1.cnt_Settle end ) as Settled_wO_pay label="Settled count without Pay",
  sum(case when t1.rpnmth=. then t1.cnt_Outstand end) as outstand label="Outstanding Count",
  t2.Notified_cnt
from WORK.RM_MOD_Windscreen t1
left outer join (
  select
    mdy(month(not_dat),01,year(not_dat)) as notif_mnth format=ddmmyy10.,
    count(distinct cats(clm_key,not_dat)) as Notified_cnt label="Reported Claims"
  from WORK.RM_MOD_Windscreen
  where notyr=repyr
  group by calculated notif_mnth
) as t2
on t1.notyr=t1.repyr and t1.repmthdt=t2.notif_mnth
group by t1.repyr,t1.repmthdt
;
quit;

Shouldn't

t1.notyr=t1.repyr

be used in a where condition? You could even move that into a dataset option:

from WORK.RM_MOD_Windscreen (where=(notyr=repyr)) t1
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX
0 Likes
Reply
Reddi
Fluorite | Level 6 Reddi
Fluorite | Level 6

Re: Proc sql Join

Posted 03-15-2017 02:15 AM (1412 views)  |   In reply to Kurt_Bremser

Hi @Kurt_Bremser,

 

Thanks for the reply,

 

The issue i was facing will be expalined if you can help me in undersating the difference between the queries attached.

 

Also It would be helpfull if you can explain how does the follwing conditions execute while joining the two tables .

 

1. from table1 t1 right join table2 t2 on  t1.repmthdt le t2.notif_mnth

2.from table1 t1 left join table2 t2 on t1.repmthdt le t2.notif_mnth

3.from table1 t1 left join table2 t2 on t2.notif_mnth le t1.repmthdt

4.from table1 t1 right join table2 t2 on t2.notif_mnth le t1.repmthdt

5.from table1 t1 right join table2 t2 on t2.notyr=t1.repyr and t1.repmthdt le t2.notif_mnth

 

Sorry i'm poor in understanding the non equal joins.

 

Sample data is below.

 

Table1:

 

Reporting yearReporting monthsettled count with payNil settled
201301/01/2013..
201301/02/20133.
201301/03/2013..
201301/04/20132.
201301/05/20131.
201301/06/20131.
201301/07/2013..
201301/08/201331
201301/09/2013.1
201301/10/2013..
201301/11/2013..
201301/12/2013..

 

Table 2:

Notif_mnthNotified_cnt
01/01/20132
01/02/20131
01/03/20137
01/06/20133

 

Thanks in advance...!


Query 1.PNGQuery 2.PNG
0 Likes
Reply
Kurt_Bremser
Super User Kurt_Bremser
Super User

Re: Proc sql Join

Posted 03-15-2017 03:01 AM (1403 views)  |   In reply to Reddi

For the types of joins, do a google search on "sas sql type of joins", and you'll find a myriad of documents dealing with this.

I also recommend to set up some test data and test the different joins for yourself, and then interpret the results in light of the knowledge you gained from those documents.

 

And, as I already stated, provide example data in a data step, as I'm not typing from the screen.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX
0 Likes
Reply
Reddi
Fluorite | Level 6 Reddi
Fluorite | Level 6

Re: Proc sql Join

Posted 03-15-2017 03:26 AM (1400 views)  |   In reply to Kurt_Bremser

Dear sir.

 

There was no much information about the non-equi joins in the google , please send me link if you find some.

 

data is below,

 

data table1;

informat repmthdt ddmmyy10.;

format repmthdt ddmmyy10.;

input repyr repmthdt Settled_with_pay Settled_wO_pay ;

cards;

2013 01/01/2013 . .

2013 01/02/2013 3 .

2013 01/03/2013 . .

2013 01/04/2013 2 .

2013 01/05/2013 1 .

2013 01/06/2013 1 .

2013 01/07/2013 . .

2013 01/08/2013 3 1

2013 01/09/2013 . 1

2013 01/10/2013 . .

2013 01/11/2013 . .

2013 01/12/2013 . .

;

run;

data table2;

informat Notif_mnth ddmmyy10.;

format Notif_mnth ddmmyy10.;

input Notif_mnth Notified_cnt ;

cards;

01/01/2013 2

01/02/2013 1

01/03/2013 7

01/06/2013 3

;

run;

 

 

0 Likes
Reply
Kurt_Bremser
Super User Kurt_Bremser
Super User

Re: Proc sql Join

Posted 03-15-2017 03:45 AM (1396 views)  |   In reply to Reddi

A non-equi join works like an equi join, it's just that the condition uses a different comparison operator. non-equi joins might result in larger outputs than equi joins, but that is to be expected, as more observations might meet the condition.

A left join takes all observations from the first dataset, and will join matching observations from the second dataset. Observations from the first dataset that have no match will be included, observations from the second dataset that have no match will be dropped.

A right join does the same thing the other way round.

 

So, in light of this, run this code:

data table1;
informat repmthdt ddmmyy10.;
format repmthdt ddmmyy10.;
input repyr repmthdt Settled_with_pay Settled_wO_pay ;
cards;
2013 01/01/2013 . .
2013 01/02/2013 3 .
2013 01/03/2013 . .
2013 01/04/2013 2 .
2013 01/05/2013 1 .
2013 01/06/2013 1 .
2013 01/07/2013 . .
2013 01/08/2013 3 1
2013 01/09/2013 . 1
2013 01/10/2013 . .
2013 01/11/2013 . .
2013 01/12/2013 . .
;
run;

data table2;
informat Notif_mnth ddmmyy10.;
format Notif_mnth ddmmyy10.;
input Notif_mnth Notified_cnt ;
cards;
01/01/2013 2
01/02/2013 1
01/03/2013 7
01/06/2013 3
;
run;

proc sql;
create table test1 as select *
from table1 t1 right join table2 t2 on t1.repmthdt le t2.notif_mnth;
create table test2 as select *
from table1 t1 left join table2 t2 on t1.repmthdt le t2.notif_mnth;
create table test3 as select *
from table1 t1 left join table2 t2 on t2.notif_mnth le t1.repmthdt;
create table test4 as select *
from table1 t1 right join table2 t2 on t2.notif_mnth le t1.repmthdt;
create table test5 as select *
from table1 t1 right join table2 t2 on year(t2.notif_mnth)=t1.repyr and t1.repmthdt le t2.notif_mnth;
quit;

proc print data=test1 noobs;
run;
proc print data=test2 noobs;
run;
proc print data=test3 noobs;
run;
proc print data=test4 noobs;
run;
proc print data=test5 noobs;
run;

And then start to interpret the results:

                        Settled_    Settled_                  Notified_
   repmthdt    repyr    with_pay     wO_pay     Notif_mnth       cnt

 01/01/2013     2013        .           .       01/01/2013        2    
 01/01/2013     2013        .           .       01/02/2013        1    
 01/01/2013     2013        .           .       01/03/2013        7    
 01/01/2013     2013        .           .       01/06/2013        3    
 01/02/2013     2013        3           .       01/02/2013        1    
 01/02/2013     2013        3           .       01/03/2013        7    
 01/02/2013     2013        3           .       01/06/2013        3    
 01/03/2013     2013        .           .       01/03/2013        7    
 01/03/2013     2013        .           .       01/06/2013        3    
 01/04/2013     2013        2           .       01/06/2013        3    
 01/05/2013     2013        1           .       01/06/2013        3    
 01/06/2013     2013        1           .       01/06/2013        3    

                        Settled_    Settled_                  Notified_
   repmthdt    repyr    with_pay     wO_pay     Notif_mnth       cnt

 01/01/2013     2013        .           .       01/01/2013        2    
 01/01/2013     2013        .           .       01/02/2013        1    
 01/02/2013     2013        3           .       01/02/2013        1    
 01/01/2013     2013        .           .       01/03/2013        7    
 01/02/2013     2013        3           .       01/03/2013        7    
 01/03/2013     2013        .           .       01/03/2013        7    
 01/01/2013     2013        .           .       01/06/2013        3    
 01/02/2013     2013        3           .       01/06/2013        3    
 01/03/2013     2013        .           .       01/06/2013        3    
 01/04/2013     2013        2           .       01/06/2013        3    
 01/05/2013     2013        1           .       01/06/2013        3    
 01/06/2013     2013        1           .       01/06/2013        3    
 01/07/2013     2013        .           .                .        .    
 01/08/2013     2013        3           1                .        .    
 01/09/2013     2013        .           1                .        .    
 01/10/2013     2013        .           .                .        .    
 01/11/2013     2013        .           .                .        .    
 01/12/2013     2013        .           .                .        .    

                        Settled_    Settled_                  Notified_
   repmthdt    repyr    with_pay     wO_pay     Notif_mnth       cnt

 01/01/2013     2013        .           .       01/01/2013        2    
 01/02/2013     2013        3           .       01/01/2013        2    
 01/03/2013     2013        .           .       01/01/2013        2    
 01/04/2013     2013        2           .       01/01/2013        2    
 01/05/2013     2013        1           .       01/01/2013        2    
 01/06/2013     2013        1           .       01/01/2013        2    
 01/07/2013     2013        .           .       01/01/2013        2    
 01/08/2013     2013        3           1       01/01/2013        2    
 01/09/2013     2013        .           1       01/01/2013        2    
 01/10/2013     2013        .           .       01/01/2013        2    
 01/11/2013     2013        .           .       01/01/2013        2    
 01/12/2013     2013        .           .       01/01/2013        2    
 01/02/2013     2013        3           .       01/02/2013        1    
 01/03/2013     2013        .           .       01/02/2013        1    
 01/04/2013     2013        2           .       01/02/2013        1    
 01/05/2013     2013        1           .       01/02/2013        1    
 01/06/2013     2013        1           .       01/02/2013        1    
 01/07/2013     2013        .           .       01/02/2013        1    
 01/08/2013     2013        3           1       01/02/2013        1    
 01/09/2013     2013        .           1       01/02/2013        1    
 01/10/2013     2013        .           .       01/02/2013        1    
 01/11/2013     2013        .           .       01/02/2013        1    
 01/12/2013     2013        .           .       01/02/2013        1    
 01/03/2013     2013        .           .       01/03/2013        7    
 01/04/2013     2013        2           .       01/03/2013        7    
 01/05/2013     2013        1           .       01/03/2013        7    
 01/06/2013     2013        1           .       01/03/2013        7    
 01/07/2013     2013        .           .       01/03/2013        7    
 01/08/2013     2013        3           1       01/03/2013        7    
 01/09/2013     2013        .           1       01/03/2013        7    
 01/10/2013     2013        .           .       01/03/2013        7    
 01/11/2013     2013        .           .       01/03/2013        7    
 01/12/2013     2013        .           .       01/03/2013        7    
 01/06/2013     2013        1           .       01/06/2013        3    
 01/07/2013     2013        .           .       01/06/2013        3    
 01/08/2013     2013        3           1       01/06/2013        3    
 01/09/2013     2013        .           1       01/06/2013        3    
 01/10/2013     2013        .           .       01/06/2013        3    
 01/11/2013     2013        .           .       01/06/2013        3    
 01/12/2013     2013        .           .       01/06/2013        3    

                        Settled_    Settled_                  Notified_
   repmthdt    repyr    with_pay     wO_pay     Notif_mnth       cnt

 01/01/2013     2013        .           .       01/01/2013        2    
 01/02/2013     2013        3           .       01/01/2013        2    
 01/02/2013     2013        3           .       01/02/2013        1    
 01/03/2013     2013        .           .       01/01/2013        2    
 01/03/2013     2013        .           .       01/02/2013        1    
 01/03/2013     2013        .           .       01/03/2013        7    
 01/04/2013     2013        2           .       01/01/2013        2    
 01/04/2013     2013        2           .       01/02/2013        1    
 01/04/2013     2013        2           .       01/03/2013        7    
 01/05/2013     2013        1           .       01/01/2013        2    
 01/05/2013     2013        1           .       01/02/2013        1    
 01/05/2013     2013        1           .       01/03/2013        7    
 01/06/2013     2013        1           .       01/01/2013        2    
 01/06/2013     2013        1           .       01/02/2013        1    
 01/06/2013     2013        1           .       01/03/2013        7    
 01/06/2013     2013        1           .       01/06/2013        3    
 01/07/2013     2013        .           .       01/01/2013        2    
 01/07/2013     2013        .           .       01/02/2013        1    
 01/07/2013     2013        .           .       01/03/2013        7    
 01/07/2013     2013        .           .       01/06/2013        3    
 01/08/2013     2013        3           1       01/01/2013        2    
 01/08/2013     2013        3           1       01/02/2013        1    
 01/08/2013     2013        3           1       01/03/2013        7    
 01/08/2013     2013        3           1       01/06/2013        3    
 01/09/2013     2013        .           1       01/01/2013        2    
 01/09/2013     2013        .           1       01/02/2013        1    
 01/09/2013     2013        .           1       01/03/2013        7    
 01/09/2013     2013        .           1       01/06/2013        3    
 01/10/2013     2013        .           .       01/01/2013        2    
 01/10/2013     2013        .           .       01/02/2013        1    
 01/10/2013     2013        .           .       01/03/2013        7    
 01/10/2013     2013        .           .       01/06/2013        3    
 01/11/2013     2013        .           .       01/01/2013        2    
 01/11/2013     2013        .           .       01/02/2013        1    
 01/11/2013     2013        .           .       01/03/2013        7    
 01/11/2013     2013        .           .       01/06/2013        3    
 01/12/2013     2013        .           .       01/01/2013        2    
 01/12/2013     2013        .           .       01/02/2013        1    
 01/12/2013     2013        .           .       01/03/2013        7    
 01/12/2013     2013        .           .       01/06/2013        3    

                        Settled_    Settled_                  Notified_
   repmthdt    repyr    with_pay     wO_pay     Notif_mnth       cnt

 01/06/2013     2013        1           .       01/06/2013        3    
 01/05/2013     2013        1           .       01/06/2013        3    
 01/03/2013     2013        .           .       01/03/2013        7    
 01/03/2013     2013        .           .       01/06/2013        3    
 01/01/2013     2013        .           .       01/03/2013        7    
 01/01/2013     2013        .           .       01/01/2013        2    
 01/01/2013     2013        .           .       01/02/2013        1    
 01/01/2013     2013        .           .       01/06/2013        3    
 01/02/2013     2013        3           .       01/03/2013        7    
 01/02/2013     2013        3           .       01/02/2013        1    
 01/02/2013     2013        3           .       01/06/2013        3    
 01/04/2013     2013        2           .       01/06/2013        3
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX
0 Likes
Reply

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • ‎03-14-2017 04:44 AM
  • 1466 views
  • 0 likes
  • 2 in conversation