DATA Step, Macro, Functions and more

SQL group by + join

Reply
Contributor
Posts: 36

SQL group by + join

Hi,

 

I got a problem and wonder if I can get some help from the community. Thanks in advance.

Suppose I have two tables:

Table1 contains :

Date  Client Score

2007-12-31 A 10

2008-12-31 A 12

2010-12-31 A 9

2008-12-31 B 8

Table2 contains only

Date

2007-12-31

2008-12-31

2009-12-31

2010-12-31

 

suppose I will have a table for each client I will have all the dates filled,

Client Date RC
A 2007-12-31 10
A 2008-12-31 12
A 2009-12-31 NA
A 2010-12-31 9
B 2007-12-31 NA
B 2008-12-31 8
B 2009-12-31 NA
B 2010-12-31 NA

 

Which join should I use?

I have tried

proc sql;

create table x as

select a.*,

b.Client as client,

coalesce(b.RC, 'NA') as RC

from test1 as a

left join test as b

on a.date=b.date

order by client, date

;

quit;

 

but I only got 5 rows back. I should get 2 Clients (A and B)X 4 Dates= 8 rows.

Someone has a suggestion?

 

Super User
Posts: 17,912

Re: SQL group by + join

Cross join

Super User
Posts: 17,912

Re: SQL group by + join

You can't have numeric and character in the same column so you may need to modify your coalesce statement. 

Contributor
Posts: 36

Re: SQL group by + join

yes I have fixed that problem

proc sql;

create table x as

select a.*,

b.Client as client,

coalesce(b.RC, 0) as RC

from test1 as a

cross join test as b

where a.date=b.date

order by client, date

;

quit;

but still i got only 4 rows

Super User
Posts: 17,912

Re: SQL group by + join

Change your where clause to On and make sure to select the date variable from table b

Contributor
Posts: 36

Re: SQL group by + join

proc sql;

create table x as

select b.date as date,

a.Client as client,

case when not missing(a.rc) then a.rc else . end as RC

from test as a

cross join test1 as b

on a.date=b.date

order by client, date

;

quit;

 

it does not work on with cross join

Super User
Posts: 17,912

Re: SQL group by + join

There should be a faster way to do this, but I haven't had coffee yet.

The following works - build your table with all the possibilities and then merge with original table.

 

data table1;
informat date anydtdte.;
format date date9.;
input Date  Client $ Score;
cards;
2007-12-31 A 10
2008-12-31 A 12
2010-12-31 A 9
2008-12-31 B 8
;
run;

data table2;
informat date anydtdte.;
format date date9.;
input date;
cards;
2007-12-31
2008-12-31
2009-12-31
2010-12-31
;
run;

proc sql;
create table empty as
select a.client, b.date
from (select distinct client from table1) as a
cross join table2 as b
order by a.client, b.date;
quit;

proc sql;
create table full as
select a.client, a.date, coalesce(b.score, 0) as score
from empty as a
full join table1 as b
on a.client=b.client 
and a.date=b.date
order by 1, 2;
quit;
Contributor
Posts: 36

Re: SQL group by + join

Thanks I will test

Contributor
Posts: 36

Re: SQL group by + join

Wow Reeza!

It works! And it has to be in two steps...thanks a lot!

Ken

Ask a Question
Discussion stats
  • 8 replies
  • 263 views
  • 0 likes
  • 2 in conversation