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?
Cross join
You can't have numeric and character in the same column so you may need to modify your coalesce statement.
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
Change your where clause to On and make sure to select the date variable from table b
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
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;
Thanks I will test
Wow Reeza!
It works! And it has to be in two steps...thanks a lot!
Ken
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.