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;
... View more
