topic Re: Union a table to itself in DI Studio in SAS Studio

Union a table to itself in DI Studio

Live — Wed, 03 Jan 2018 17:01:23 GMT

Hi,

I am a novice in DI Studio (version 4.9) and was hoping someone here will be able to help.

I have a table which I would like to union to itself several times, but each time I will bring back only 4 columns where two are the same, and two contains the same information and should be renamed to be the same, however each time there is a where statement included. In Base SAS, the code looks like this:

select T1."ACCOUNT_ID"
, T1."STATUS_CODE_1" "STATUS_CODE"
, T1."REASON_CODE_1" "REASON_CODE"
, T1."CLOSE_DATE"
from "Q$MARKREP"."TSYS_CARD_ACCOUNTS_DIM_MARKREP" T1
where not T1."STATUS_CODE_1" is null
union all
select T1."ACCOUNT_ID"
, T1."STATUS_CODE_2" "STATUS_CODE"
, T1."REASON_CODE_2" "REASON_CODE"
, T1."CLOSE_DATE"
from "Q$MARKREP"."TSYS_CARD_ACCOUNTS_DIM_MARKREP" T1
where not T1."STATUS_CODE_2" is null
union all
select T1."ACCOUNT_ID"
, T1."STATUS_CODE_3" "STATUS_CODE"
, T1."REASON_CODE_3" "REASON_CODE"
, T1."CLOSE_DATE"
from "Q$MARKREP"."TSYS_CARD_ACCOUNTS_DIM_MARKREP" T1
where not T1."STATUS_CODE_3" is null;

(which needs to be repeated 82 times).

I have tried to do this using the join transformation or the Set Operation transformation, but I keep getting stuck when I try to get the table in a second time.

Anyone have a solution to this?

Many Thanks

Live

Re: Union a table to itself in DI Studio

snoopy369 — Wed, 03 Jan 2018 22:25:30 GMT

I think you probably should do a Transpose transformation rather than trying to use SQL. The SAS code equivalent would be PROC TRANSPOSE, and I imagine there's no reason to do otherwise in DI Studio ...

Re: Union a table to itself in DI Studio

Live — Thu, 04 Jan 2018 08:41:36 GMT

would this not create a wide table of the result rather than a long where each result appears under the last?

I was thinking that I need to create a new table for each of the STATUS_CODE criteria (i.e create 82 smaller tables, where the column names in each table is the same), and then union these together 82 table together. However is there a better way of doing this?

Re: Union a table to itself in DI Studio

snoopy369 — Thu, 04 Jan 2018 15:17:38 GMT

No, it wouldn't necessarily make a wide table. TRANSPOSE goes either direction.

I am not a DI Studio expert, so I'll show you in SAS code; but i'm fairly sure DI Studio can do this, and if not you can always just do it in code directly.

You use your variables that define an entity (that you select on every single 'table') as BY variables, so account_id and close_date, and then each of the variable _prefixes_ you want to end up transposed wide to long in the final table would need a separate TRANSPOSE (so, two transposes I think, one for reason codes and one for status codes). Then you join the two together in a simple left join. The weird bit of the end of the ON clause is a fancy way to get the numeric part of the variable name (so you are joining reason_Code_3 to status_code_3), but you can do it in easier ways if you like.


data have;
input 
  account_id
  close_date :date9.
  status_code_1-status_code_3
  reason_code_1-reason_code_3
;
datalines;
1 01JAN2015 . 2 3 1 2 3
2 01FEB2015 4 5 6 4 5 6
3 01MAR2015 7 8 9 1 2 3
;;;;
run;

proc transpose data=have out=status( where=(not missing(status_code1))) prefix=status_code;
 by account_id close_date;
 var status_code_:;
run;

proc transpose data=have out=reason( where=(not missing(reason_code1))) prefix=reason_code;
 by account_id close_date;
 var reason_code:;
run;

proc sql;
 create table want as
  select status.account_id, status.close_date, status.status_code1 as status_code, reason.reason_code1 as reason_code
   from status left join reason
    on status.account_id eq reason.account_id and status.close_date eq reason.close_date 
       and scan(status._name_,-1,'_','a') = scan(reason._name_,-1,'_','a');
quit;

Re: Union a table to itself in DI Studio

Live — Fri, 05 Jan 2018 13:21:37 GMT

Thank you! this is exactly what I need as the outcome. I will try and figure out how I can use the transformations in DI Studio to do this, if not, I will replicate the example code you have given me.