Working in an SQL Join transformation in DI Studio, I need an expression which says that "All fields in this column should have the same value as the field where Current=1".
Only the rows with Current=1 has "disability_flag" filled out, and I need to copy it to those with Current=0.
In example, this input table:
PK | Disability_Flag | Current |
---|---|---|
1 | 0 | |
1 | 1 | 1 |
2 | 0 | |
2 | 0 | |
2 | 0 | 1 |
Should become this output table:
PK | Disability_Flag | Current |
---|---|---|
1 | 1 | 0 |
1 | 1 | 1 |
2 | 0 | 0 |
2 | 0 | 0 |
2 | 0 | 1 |
For some reason I can't think of what expression to use. I'd appreciate any advice, thanks.
Join the table onto itself (aliases lets say a and b), on PK.
mapping:
b.disability_flg
Where:
b.current = '1'
Join the table onto itself (aliases lets say a and b), on PK.
mapping:
b.disability_flg
Where:
b.current = '1'
Thanks Linus. I know how to join tables to themselves if I can drag-and-drop them from the folders menu into the SQL Join "Create" window, but in this case the input table is a previous SQL Join. How can I make a copy of it in the DI Studio SQL Join Create window?
I had hoped to resolve the problem another way. So far I've been grouping by the PK and used expressions to select for example the min(ValidFrom_DTTM) and max(ValidTo_DTTM) to combine 2-3 rows into one. My aim is that the combined row should have the most updated/current disability_flag value.
Just map it twice in the job diagram, the second mapping prompts you for specifying an alias.
Perfect, now all the other pieces fell into place as well. Thank you.
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.
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.