topic Re: Help with simple SQL Join expression in SAS Data Management

Help with simple SQL Join expression

TurnTheBacon — Tue, 08 Jan 2013 08:54:08 GMT

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.

Re: Help with simple SQL Join expression

LinusH — Tue, 08 Jan 2013 09:19:27 GMT

Join the table onto itself (aliases lets say a and b), on PK.

mapping:

b.disability_flg

Where:

b.current = '1'

Re: Help with simple SQL Join expression

TurnTheBacon — Tue, 08 Jan 2013 10:03:06 GMT

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.

Re: Help with simple SQL Join expression

LinusH — Tue, 08 Jan 2013 10:12:19 GMT

Just map it twice in the job diagram, the second mapping prompts you for specifying an alias.

Re: Help with simple SQL Join expression

TurnTheBacon — Tue, 08 Jan 2013 10:21:16 GMT

Perfect, now all the other pieces fell into place as well. Thank you.