FULL JOIN

[fredbell]

Hello everyone!

What do i need to do to get it to return the results from B in every row not just the first for each?


PROC SQL;
CREATE TABLE TEST AS
SELECT A.DATE,A.DAY,A.HANDLED,A.LEVEL1DESC, P.UCL,P.LCL
FROM MEAN1 A FULL OUTER JOIN DAILY_MEAN P
ON A.LEVEL1DESC = P.LEVEL1DESC AND A.DAY = P.DAY

ORDER BY A.LEVEL1DESC, A.DATE;
QUIT;



fred

Message was edited by: fredbell Message was edited by: fredbell

[Florent]

Hello,

What if you replace your 'full outer join' by a 'cross join' ?

It gives the following query:

PROC SQL;
CREATE TABLE TEST AS
SELECT A.DATE,A.DAY,A.HANDLED,A.LEVEL1DESC, P.UCL,P.LCL
FROM MEAN1 A CROSS JOIN DAILY_MEAN P
ON A.LEVEL1DESC = P.LEVEL1DESC AND A.DAY = P.DAY

ORDER BY A.LEVEL1DESC, A.DATE;
QUIT;



Regards,
Florent

[darrylovia]

do you mean something like this where you have a big table and a "look-up" table witha single row and want to put the data from the single row table on the big table?

See the below example for using the SASHELP table SHOES which comes in every SAS installation


proc sql;
create table average_sales as
select mean(sales) as avg_sales format=dollar12.
from sashelp.shoes;

create table shoes_with_avg_sales as
select S.*, a.avg_sales
from sashelp.shoes S cross join average_sales a
;
quit;

[fredbell]

Thanks for the input people, it turns out that it was because the day was a number type.

I was instructed to use the put(day) to transform it back to a text,

From text to sas data format then back to text, what a pain in the PUT.

Thanks

Fred