Date: Fri, 18 May 2001 10:09:19 -0400
Reply-To: Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject: Re: SAS and SQL
Content-Type: text/plain; charset="iso-8859-1"
Fabrice,
It is faulty reasoning to think if anyone sends me a two step solution that
there is then not a one step solution (even when the solutions come from the
cream of SAS-L).
Using Ya's data, but my interpretation that the resulting file should
include all cities in B and only cities in B leads to one step solution
(albeit A is read twice).
data a ( index = ( city ) ) ;
input id city $15.;
cards;
1 New York
2 Boston
3 San Diego
4 Washington
;
data b;
input city $1-15 pop $;
cards;
New York 9M
Boston 5M
LA 10M
Dallas 7M
;
data w ( drop = maxid ) ;
retain maxid ;
if _n_ = 1 then
do ;
do until ( eof ) ;
set a end = eof ;
if maxid < id then maxid = id ;
end ;
end ;
set b ;
set a key = city / unique ;
if _iorc_ ^= 0 then
do ;
maxid + 1 ;
id = maxid ;
_error_ = 0 ;
end ;
run ;
If the files were sorted by City then the bottom half of the step could be
replaced by a simple MERGE using an IN variable. Of course, joining in SQL
may make it look like one step and do the sorts anyway.
If you do not like the performance or the fact that A is read twice, then
Paul Dorfman has shown many lookup examples on SAS-L using hashing which
would do the lookup in an array and allow the building of the array and the
initial MAXID to be combined into one reading of A. This would provide the
fastest solution at the expense of a little research work.
Ian Whitlock <whitloi1@westat.com>
-----Original Message-----
From: ROTH Fabrice [mailto:fabrice.roth@STATEC.ETAT.LU]
Sent: Friday, May 18, 2001 2:05 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SAS and SQL
--------------C4B4C2A23F4D4EDA5F791468
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Hi Huang,
Thank you. That bring me a lot further. At least I know that there is no
one step possibility.
Sincerely
"Huang, Ya" wrote:
>
>
> Hi Roth,
>
> I don't know if it can be done in one sql step,
> I know how it can be done in two steps (sql + data step):
>
> data a;
> input id city $15.;
> cards;
> 1 New York
> 2 Boston
> 3 San Diego
> 4 Washington
> ;
>
> data b;
> input city $1-15 pop $;
> cards;
> New York 9M
> Boston 5M
> LA 10M
> Dallas 7M
> ;
>
> proc sql;
> create table a as
> select a.id, coalesce(a.city,b.city) as city, max(id) as maxid
> from a full join b
> on a.city=b.city
> ;
>
> data a (drop=newid maxid);
> set a;
> retain newid;
> if _n_=1 then newid=maxid+1;
> if id=. then do id=newid; newid+1; end;
>
> options nocenter;
> proc print;
> run;
> ---------------------
> The SAS System 08:15 Thursday, May 17, 2001 18
>
> Obs id city
>
> 1 2 Boston
> 2 5 Dallas
> 3 6 LA
> 4 1 New York
> 5 3 San Diego
> 6 4 Washington
>
> HTH
>
> Ya Huang
>
> -----Original Message-----
> From: ROTH Fabrice [mailto:fabrice.roth@STATEC.ETAT.LU]
> Sent: Thursday, May 17, 2001 7:57 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: SAS and SQL
>
> Hi all
>
> I've got the following problem:
>
> I have two tables let's say A and B.
> In table A I have a key (number) which identifies a city. Associated
> to
> that city I have the name in english.
> In table B I have a name of a city with a lot of other data.
>
> What I now need is the following:
> If a city of record x in Table B is in Table A then I want to
> extract the key-number of that city.
> If the city of record x in Table B is not in Table A, then I need
> to
> create a key number (largest+1) and fill in the name of the city.
>
> What I don't know is how I can in fact put an IF condition in a
> SQL-Statement or call an SQL-statement in a data-step.
>
> I would appreciate any advise.
>
> Thanks.
>
> --
> ROTH Fabrice
>
> STATEC
> 6 Bd. Royal
> L-2013 Luxembourg
> Tel: +352 478-4385
> Fax: +352 467734
> fabrice.roth@statec.etat.lu
> http://www.statec.lu
--
ROTH Fabrice
STATEC
6 Bd. Royal
L-2013 Luxembourg
Tel: +352 478-4385
Fax: +352 467734
fabrice.roth@statec.etat.lu
http://www.statec.lu
--------------C4B4C2A23F4D4EDA5F791468
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
Hi Huang,
<p>Thank you. That bring me a lot further. At least I know that there is
no one step possibility.
<br>
<p>Sincerely
<br>
<p>"Huang, Ya" wrote:
<blockquote TYPE=CITE>
<p><font size=-1>Hi Roth,</font>
<p><font size=-1>I don't know if it can be done in one sql step,</font>
<br><font size=-1>I know how it can be done in two steps (sql + data
step):</font>
<p><font size=-1>data a;</font>
<br><font size=-1>input id city $15.;</font>
<br><font size=-1>cards;</font>
<br><font size=-1>1 New York</font>
<br><font size=-1>2 Boston</font>
<br><font size=-1>3 San Diego</font>
<br><font size=-1>4 Washington</font>
<br><font size=-1>;</font>
<p><font size=-1>data b;</font>
<br><font size=-1>input city $1-15 pop $;</font>
<br><font size=-1>cards;</font>
<br><font size=-1>New York 9M</font>
<br><font size=-1>Boston
5M</font>
<br><font
size=-1>LA
10M</font>
<br><font size=-1>Dallas
7M</font>
<br><font size=-1>;</font>
<p><font size=-1>proc sql;</font>
<br><font size=-1>create table a as</font>
<br><font size=-1>select a.id, coalesce(a.city,b.city) as city, max(id)
as maxid</font>
<br><font size=-1>from a full join b</font>
<br><font size=-1>on a.city=b.city</font>
<br><font size=-1>;</font>
<p><font size=-1>data a (drop=newid maxid);</font>
<br><font size=-1> set a;</font>
<br><font size=-1> retain newid;</font>
<br><font size=-1> if _n_=1 then newid=maxid+1;</font>
<br><font size=-1> if id=. then do id=newid; newid+1; end;</font>
<p><font size=-1>options nocenter;</font>
<br><font size=-1>proc print;</font>
<br><font size=-1>run;</font>
<br><font size=-1>---------------------</font>
<br><font size=-1>The SAS System 08:15 Thursday, May 17,
2001
18</font>
<p><font size=-1>Obs id city</font>
<p><font size=-1> 1 2
Boston</font>
<br><font size=-1> 2 5
Dallas</font>
<br><font size=-1> 3 6
LA</font>
<br><font size=-1> 4 1
New York</font>
<br><font size=-1> 5 3
San Diego</font>
<br><font size=-1> 6 4
Washington</font>
<p><font size=-1>HTH</font>
<p><font size=-1>Ya Huang</font>
<p><font size=-1>-----Original Message-----</font>
<br><font size=-1>From: ROTH Fabrice [<a
href="mailto:fabrice.roth@STATEC.ETAT.LU">mailto:fabrice.roth@STATEC.ETAT.LU
</a>]</font>
<br><font size=-1>Sent: Thursday, May 17, 2001 7:57 AM</font>
<br><font size=-1>To: SAS-L@LISTSERV.UGA.EDU</font>
<br><font size=-1>Subject: SAS and SQL</font>
<p><font size=-1>Hi all</font>
<p><font size=-1>I've got the following problem:</font>
<p><font size=-1>I have two tables let's say A and B.</font>
<br><font size=-1>In table A I have a key (number) which identifies a city.
Associated to</font>
<br><font size=-1>that city I have the name in english.</font>
<br><font size=-1>In table B I have a name of a city with a lot of other
data.</font>
<p><font size=-1>What I now need is the following:</font>
<br><font size=-1> If a city of record x in Table B is
in Table A then I want to</font>
<br><font size=-1>extract the key-number of that city.</font>
<br><font size=-1> If the city of record x in Table B
is not in Table A, then I need to</font>
<br><font size=-1>create a key number (largest+1) and fill in the name
of the city.</font>
<p><font size=-1>What I don't know is how I can in fact put an IF condition
in a</font>
<br><font size=-1>SQL-Statement or call an SQL-statement in a
data-step.</font>
<p><font size=-1>I would appreciate any advise.</font>
<p><font size=-1>Thanks.</font>
<p><font size=-1>--</font>
<br><font size=-1>ROTH Fabrice</font>
<p><font size=-1>STATEC</font>
<br><font size=-1>6 Bd. Royal</font>
<br><font size=-1>L-2013 Luxembourg</font>
<br><font size=-1>Tel: +352 478-4385</font>
<br><font size=-1>Fax: +352 467734</font>
<br><font size=-1>fabrice.roth@statec.etat.lu</font>
<br><font size=-1><a href="http://www.statec.lu"
TARGET="_blank">http://www.statec.lu</a></font></blockquote>
<p>--
<br>ROTH Fabrice
<p>STATEC
<br>6 Bd. Royal
<br>L-2013 Luxembourg
<br>Tel: +352 478-4385
<br>Fax: +352 467734
<br>fabrice.roth@statec.etat.lu
<br><A HREF="http://www.statec.lu">http://www.statec.lu</A>
<br> </html>
--------------C4B4C2A23F4D4EDA5F791468--
