BookmarkSubscribeRSS Feed
ken2
Obsidian | Level 7

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?

 

8 REPLIES 8
Reeza
Super User

Cross join

Reeza
Super User

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

ken2
Obsidian | Level 7

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
Super User

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

ken2
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

Thanks I will test

ken2
Obsidian | Level 7

Wow Reeza!

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

Ken

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1863 views
  • 0 likes
  • 2 in conversation