MRDM,
The uniqueness [and non-standardness] of the task is that a) as you mentioned, you have no change indicator [which is not typical], and b) you want to do very specific non-SCD type of transformation [close the record, recording the end date as a change to the record, but not keep the history]. This makes the task *slightly* more challenging.
First off, the Update, Merge, and most of the other DI transformations in the SQL group (except Join) are for in-database work and only with certain RDBMS (and, as you noted, not ODBC). The proper SQL code to perform this task would be an UPDATE, but the only transformation we have to work with here is Join -- so we would end up hand-coding the solution (which you didn't want to do).
It could also be done in SAS Data Step fairly easily, but again this requires hand-coding.
So we have to convince DI Studio of the right thing to do. Here's how I accomplished it [I've attached an image of the flow].
- I added a SQL Join {xform #1} to simply add a column named CLOSE to the SOURCE table giving it the value 1.
- I added a SQL Join {xform #2} to join (a LEFT join of MASTER and SOURCE) ensuring that CLOSE flows through on the matching records. This effectively tags MASTER records as needed to be closed.
- I added a (very powerful!) Data Validation {xform #3} node and created a Custom Validation with:
condition: close=1
if condition is true:
is_current=0
record_end_date=datetime()
This does the transformations as you need them.
- I added a Table Loader {xform #4} writing out to the Master_Updated table (you could overwrite the MASTER if desired) so that I can drop the CLOSE column.
I'm sure there are other ways to accomplish this as well. But this *was* accomplished with only point-and-click and no hand-coding. Thats the nice thing about DI Studio, it usually gives you many different ways tasks can be accomplished.
Were you using DB2/Oracle/SQL Server/Sybase/Teradata, the Update transformation would (by far) be the correct way to go.
Steve
I believe at least with a recent version of DIS the SQL UPDATE transformation should allow you to do what you're after.
What I'm normally doing when I'm not sure how to set-up stuff in DIS: I first figure out how the generated code should look like and then try and click it together in DIS while checking in the generated code if I'm getting what I want.
For your use case the code I'd go for is a SQL Update with an EXIST clause. Here the test code:
data master;
input ID Store_Number Local_Cust_ID Global_Cust_ID (Record_Start_Date Record_End_Date) (:datetime.) Is_Current;
format Record_Start_Date Record_End_Date datetime21.;
datalines;
1 987 78 1989 11APR2017:11:49:46.850 01JAN5999:00:00:00.000 1
2 321 54 1989 11APR2017:09:49:46.850 01JAN5999:00:00:00.000 1
;
run;
data trans;
input Store_Number Local_Cust_ID Global_Cust_ID;
datalines;
987 78 1989
654 64 4589
;
run;
proc sql;
update master as m
set
Record_End_Date="%sysfunc(datetime(),datetime20.)"dt,
Is_Current=0
where exists
(
select * from trans as t
where m.Store_Number=t.Store_Number and
m.Local_Cust_ID=t.Local_Cust_ID and
m.Global_Cust_ID=t.Global_Cust_ID
)
;
quit;
Using DIS 4.901 I was able to generate the same code as follows:
1. Job Flow
2. SQL Update transformation:
3. Subquery (highlighted above):
Generated code:
Let me know if that worked for you.
P.S: If you're using an ODBC connection then - depending where your data reside (SAS or database) - it might be worth to also play around with all the ODBC settings and pass-through SQL.
If you go for pass-through SQL then make sure that passing in the datetime works. You need eventually to use a database specif function to set the current date - just check the generated code and if the %sysfunc(datetime()...) bit appears in the pass-through SQL block then replace this in the expression with a database function which returns the current datetime.
You've got subqueries also in the SQL Join transformation. Having said that I'm not a great fan of how the DIS windows behave for sub-query definitions. I'd like to get something like a pop-up window or a split screen for sub-queries so I can keep a graphical overview over the whole SQL while working on a sub-query.
I consider SQL's with sub-queries also much harder to debug so I normally try to keep my SQLs as simple as possible.
Given that SQL views are in a way nothing else than encapsulated SQL code what I'm normally doing if possible, is to have multiple nodes and to define the "sub-queries" upfront in their own SQL nodes creating a view - and then use these views in the downstream "master" query. This allows me to unit test and debug these "sub-queries" separately.
This is of course not always possible (ie. for an EXIST clause) and if interfacing with a database you want also to make sure that the SAS/Access engine doesn't get confused and is still able to send as much of the full SQL as possible to the database (use options sastrace=',,,d' sastraceloc=saslog nostsuffix; in such a case to verify in the log what part of the SQL gets actually sent to the database for in-database processing).
If going for this "upfront view" approach also use the FEEDBACK option for your main query.
proc sql;
create view v_class as
select *
from sashelp.class
;
quit;
proc sql feedback;
create table test as
select a.*
from
sashelp.class as a
inner join
v_class as b
on a.name=b.name
;
quit;
Using the FEEDBACK option you then see in the log what SAS actually makes out of your code.
NOTE: Statement transforms to:
select A.Name, A.Sex, A.Age, A.Height, A.Weight
from SASHELP.CLASS A inner join
( select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight
from SASHELP.CLASS
) on A.Name = CLASS.Name;
And as DIS flow above would look like:
And you can set the FEEDBACK option here (as part of SQL Join node "Join with Class"):
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.
-
6401 views
-
8 likes
-
-
