Prev: Who is Moderating this group?
Next: Extract Top 10 scores of student in different Class
From: Ben Adams on 23 Jul 2010 04:42
Hi,

Apologies for the simplistic nature of my enquiry but I am having a
comlete mental block..

I have two datasets and I want to update a column in dataset 1 where
the area and date fields match those in dataset 2.

in SQL it would be something like:

Update table1
set flag = 'T'
from table1 left join table2
on table1.date = table2.date
and table1.area = table2.area;

Thanks for you help...

ben

From: Patrick on 23 Jul 2010 05:14
Hi Ben

If you feel comfortable with SQL then why not use SQL?

PROC SQL;
Update table1
set flag = 'T'
from table1 left join table2
on table1.date = table2.date
and table1.area = table2.area;
QUIT;

With a SAS data step you coud use either:
- modify/update
- a hash lookup
- a format
- a merge


HTH
Patrick
From: Ben Adams on 23 Jul 2010 05:58
On Jul 23, 10:14 am, Patrick <patrick.mat...(a)gmx.ch> wrote:
> Hi Ben
>
> If you feel comfortable with SQL then why not use SQL?
>
> PROC SQL;
> Update table1
> set flag = 'T'
> from table1 left join table2
> on table1.date = table2.date
> and table1.area = table2.area;
> QUIT;
>
> With a SAS data step you coud use either:
> - modify/update
> - a hash lookup
> - a format
> - a merge
>
> HTH
> Patrick

Thanks Patrick,

The sql gives me a syntax error, it doesn't seem to like the FROM part
of the SQL?
ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *,
**, +, ',', -, /, WHERE, ||.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 202-322: The option or parameter is not recognized and will be
ignored.


ben
From: Patrick on 23 Jul 2010 08:03
Hi Ben

That's because your SQL statement is not correct. The correct syntax
looks like:

PROC SQL;
Update table1
set flag = (select 'T' from table2 where table1.date = table2.date
and table1.area = table2.area)
;
QUIT;

HTH
Patrick
From: Patrick on 23 Jul 2010 08:16
Hi Ben

If your tables are in SAS then a hash lookup will certainly perform a
bit better.

Same result than with the SQL code - only done in a different way.

data test1;
set table1;
if _n_=1 then
do;
declare hash h (dataset:'work.table2');
rc = h.defineKey('area','date');
rc = h.defineDone();
end;
if (h.check()=0) then
do;
flag='T';
end;
run;

HTH
Patrick
 | 
Pages: 1
Prev: Who is Moderating this group?
Next: Extract Top 10 scores of student in different Class