topic Re: FULL JOIN in SAS Procedures

FULL JOIN

fredbell — Fri, 19 Nov 2010 15:42:05 GMT

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

Re: FULL JOIN

Florent — Tue, 23 Nov 2010 11:50:18 GMT

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

Re: FULL JOIN

darrylovia — Tue, 23 Nov 2010 13:15:05 GMT

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;

Re: FULL JOIN

fredbell — Tue, 23 Nov 2010 23:29:25 GMT

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