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?
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
Tried this already, but wasn't succesfull. I have to retry on Monday.
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").
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.