topic Re: proc sql, comparing between two inputs in SAS Programming

proc sql, comparing between two inputs

PaulC — Fri, 19 Sep 2014 15:32:08 GMT

I'm using proc sql in data integration studio transformation "sql execute". How can I make the following work so that I can delete from the table according to the other table.

I have two inputs to the transformation &_input1. and &_input2.

There is just some simple syntax error which I do not see ?

user written code:

proc sql;

delete from &_input2.

where &_input2.variable = &_input1.variable

quit;

I cannot point to input one as: "&_input1.variable", how do I do that?

Re: proc sql, comparing between two inputs

ballardw — Fri, 19 Sep 2014 15:40:39 GMT

If &_input1 and 2 are dataset names you need two periods to reference variables to get the dataset.variable construct

the first period tells the macro processor to get the value of &_input1 and then append the text variable

where &_input2..variable = &_input1..variable

Re: proc sql, comparing between two inputs

PaulC — Sat, 20 Sep 2014 04:48:58 GMT

Tried this already, but wasn't succesfull. I have to retry on Monday.

Re: proc sql, comparing between two inputs

Patrick — Sat, 20 Sep 2014 10:32:08 GMT

Code you could generate using the SQL Delete transformation:

proc sql;

delete from target.Class_Target

where

Class_Target.Name IN (

select

DelRecords.Name length = 8

from

work.DelRecords as DelRecords

)

;quit;

Below how my sample job creating above code looks like.

I first load sashelp.class into a target table, then have an extract step where I only select the rows "where name='Alfred' from the source table, and then a delete step deleting all records in target where name matches with the extract (so: "Alfred").

Re: proc sql, comparing between two inputs

PaulC — Tue, 23 Sep 2014 15:31:04 GMT

I found out that this is really what i want and working:

proc sql;

DELETE FROM &_input2. as b
WHERE EXISTS(SELECT 1 FROM &_input1. as a WHERE a.variable1=b.variable1 and a.variable2=b.variable2);

quit;