proc sql, comparing between two inputs

[PaulC]

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?

[ballardw]

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

[PaulC]

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

[Patrick]

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").

[PaulC]

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;