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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.