Help using Base SAS procedures

FULL JOIN

Reply
Contributor
Posts: 73

FULL JOIN

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
Frequent Contributor
Posts: 127

Re: FULL JOIN

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
Frequent Contributor
Posts: 139

Re: FULL JOIN

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;
Contributor
Posts: 73

Re: FULL JOIN

Posted in reply to darrylovia
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
Ask a Question
Discussion stats
  • 3 replies
  • 187 views
  • 0 likes
  • 3 in conversation