BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Live
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
snoopy369
Barite | Level 11

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 solution in original post

4 REPLIES 4
snoopy369
Barite | Level 11

 

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 ...

Live
Calcite | Level 5

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?

snoopy369
Barite | Level 11

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;

 

Live
Calcite | Level 5

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.

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1031 views
  • 1 like
  • 2 in conversation