SQL group by + join

[ken2]

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?

[Reeza]

Cross join

[Reeza]

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

[ken2]

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

[Reeza]

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

[ken2]

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

[Reeza]

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;

[ken2]

Thanks I will test

[ken2]

Wow Reeza!

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

Ken