Prev: Windows client connecting to Linux server (SAS/Connect)
Next: Excel Workbook Setting Error Message
From: siyuan li on 8 Dec 2009 07:44
hi, I am sorry first for that I ask the same problem.
here has 2 datasets:
data a;
input code manager$;
cards;
145 max
145 xam
155 paul
165 kobe
;
run;
data b;
input code assistant$;
cards;
145 jerry
145 tracy
145 wade
155 chen
170 yi
;
run;
data c;
merge a b;
by code;
run;
the question is that I want to yield the same output to use proc sql
full join procedure, I try to use the procudure below,but it did not
yield the same output,can anyone help me?
proc sql;
select a.code,a.manager,b.assistant from a full join b
on a.code=b.code;
quit;

thanks in advance
From: yingtao on 8 Dec 2009 10:46
I don't think SQL could achieve what merge does in this scenario.

PROC SQL does not process joins according to the position of values in
BY groups.
Instead, PROC SQL processes data only according to the data values.

SQL produces Cartesian product, and you could use Coalesce function to
list all the "code" from both
datasets.

proc sql;
create table d as
select COALESCE(a.code,b.code) as code, a.manager,b.assistant
from a full join b
on a.code=b.code;
quit;

Tao
From: Toby Dunn on 8 Dec 2009 13:53
SQL is a group processing language where as the Data Step is a Rox or
observational processing language. So A join and a Merge are not the
equivalent creatures. While it may be possible in some cases to mimic the
Merge with a Join it isn't always the best solution. In this case the
extra effort it would take to massage the input data amd the extra SQL
code isn't worth the effort. We as SAS programmers have both at our
disposal, the import thing is to recognize when one is better suited for
the task than the other and use it.

On the other hand if this is school work your on your own.


Toby Dunn


On Tue, 8 Dec 2009 04:44:12 -0800, siyuan li <lisiyuan0753(a)GMAIL.COM>
wrote:

>hi, I am sorry first for that I ask the same problem.
>here has 2 datasets:
> data a;
>input code manager$;
>cards;
>145 max
>145 xam
>155 paul
>165 kobe
>;
>run;
>data b;
>input code assistant$;
>cards;
>145 jerry
>145 tracy
>145 wade
>155 chen
>170 yi
>;
>run;
>data c;
>merge a b;
>by code;
>run;
>the question is that I want to yield the same output to use proc sql
>full join procedure, I try to use the procudure below,but it did not
>yield the same output,can anyone help me?
> proc sql;
>select a.code,a.manager,b.assistant from a full join b
>on a.code=b.code;
>quit;
>
>thanks in advance
From: "Choate, Paul on 8 Dec 2009 16:24
As Toby said - SQL and the data step work differently - and while it's not to hard to make a data step mimic a SQL join the reverse isn't always so easy.

The key is the uniqueness of the rows - so the most straightforward solution is to make your rows unique...

data a;
input code manager $;
cards;
145 max
145 xam
155 paul
165 kobe
;

data b;
input code assistant $;
cards;
145 jerry
145 tracy
145 wade
155 chen
170 yi
;

data a;
set a;
by code;
if first.code then n=0;
n+1;
run;

data b;
set b;
by code;
if first.code then n=0;
n+1;
run;

data C_DATASTEP(drop=n);
merge a b;
by code n;
run;

proc sql;
create TABLE C_SQL as
select COALESCE(a.code,b.code) as code,
a.manager, b.assistant
from a full join b
on a.code=b.code and a.n=b.n;
quit;



Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of siyuan li
Sent: Tuesday, December 08, 2009 4:44 AM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: about full join problem

hi, I am sorry first for that I ask the same problem.
here has 2 datasets:
data a;
input code manager$;
cards;
145 max
145 xam
155 paul
165 kobe
;
run;
data b;
input code assistant$;
cards;
145 jerry
145 tracy
145 wade
155 chen
170 yi
;
run;
data c;
merge a b;
by code;
run;
the question is that I want to yield the same output to use proc sql
full join procedure, I try to use the procudure below,but it did not
yield the same output,can anyone help me?
proc sql;
select a.code,a.manager,b.assistant from a full join b
on a.code=b.code;
quit;

thanks in advance
From: Toby Dunn on 8 Dec 2009 16:35
Paul,

You cheated in so much as you also used the Unique key on the data step
merge. If you try to use your data in SQL and mimic the posters original
data step your SQL code wont work.

On Tue, 8 Dec 2009 13:24:59 -0800, Choate, Paul(a)DDS
<Paul.Choate(a)DDS.CA.GOV> wrote:

>As Toby said - SQL and the data step work differently - and while it's
not to hard to make a data step mimic a SQL join the reverse isn't always
so easy.
>
>The key is the uniqueness of the rows - so the most straightforward
solution is to make your rows unique...
>
>data a;
>input code manager $;
>cards;
>145 max
>145 xam
>155 paul
>165 kobe
>;
>
>data b;
>input code assistant $;
>cards;
>145 jerry
>145 tracy
>145 wade
>155 chen
>170 yi
>;
>
>data a;
> set a;
> by code;
> if first.code then n=0;
> n+1;
>run;
>
>data b;
> set b;
> by code;
> if first.code then n=0;
> n+1;
>run;
>
>data C_DATASTEP(drop=n);
> merge a b;
> by code n;
>run;
>
>proc sql;
> create TABLE C_SQL as
> select COALESCE(a.code,b.code) as code,
> a.manager, b.assistant
> from a full join b
> on a.code=b.code and a.n=b.n;
>quit;
>
>
>
>Paul Choate
>DDS Data Extraction
>(916) 654-2160
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
siyuan li
>Sent: Tuesday, December 08, 2009 4:44 AM
>To: SAS-L(a)LISTSERV.UGA.EDU
>Subject: about full join problem
>
>hi, I am sorry first for that I ask the same problem.
>here has 2 datasets:
> data a;
>input code manager$;
>cards;
>145 max
>145 xam
>155 paul
>165 kobe
>;
>run;
>data b;
>input code assistant$;
>cards;
>145 jerry
>145 tracy
>145 wade
>155 chen
>170 yi
>;
>run;
>data c;
>merge a b;
>by code;
>run;
>the question is that I want to yield the same output to use proc sql
>full join procedure, I try to use the procudure below,but it did not
>yield the same output,can anyone help me?
> proc sql;
>select a.code,a.manager,b.assistant from a full join b
>on a.code=b.code;
>quit;
>
>thanks in advance
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Windows client connecting to Linux server (SAS/Connect)
Next: Excel Workbook Setting Error Message