09-19-2014 11:32 AM
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:
delete from &_input2.
where &_input2.variable = &_input1.variable
I cannot point to input one as: "&_input1.variable", how do I do that?
09-19-2014 11:40 AM
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
09-20-2014 06:32 AM
Code you could generate using the SQL Delete transformation:
delete from target.Class_Target
Class_Target.Name IN (
DelRecords.Name length = 8
work.DelRecords as DelRecords
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").
09-23-2014 11:31 AM
I found out that this is really what i want and working:
DELETE FROM &_input2. as b
WHERE EXISTS(SELECT 1 FROM &_input1. as a WHERE a.variable1=b.variable1 and a.variable2=b.variable2);