SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio User Transformation SQL Join

Reply
Occasional Contributor
Posts: 5

DI Studio User Transformation SQL Join

Hi,

 

I would like to create a User Transformation in DI Studio that creates a PROC SQL and joins two datasets. The thing is, it seems SAS doesn't create macrovariables for source dataset for the output variables.

 

For example, I would like to construct the following code:

PROC SQL ;
  CREATE TABLE &_output. AS
    SELECT ds1.name AS name_1
          ,ds2.name AS name_2
    FROM work.ds1
    LEFT JOIN work.ds2 ON ds1.name = ds2.name
  ;
QUIT ;

 

I can't find the macrovars for ds1 in the select statement.


Anyone has an idea how to solve this?

 

Thierry

Senior User
Posts: 1

Re: DI Studio User Transformation SQL Join

[ Edited ]

Hi,

When you connect your two tables to the UWT it will be available var _INPUT1 and _INPUT2. For output you can use var _OUTPUT

Best Regards,
João

Occasional Contributor
Posts: 5

Re: DI Studio User Transformation SQL Join

[ Edited ]

I'm actually looking for the table that is the source for output variable name_1, ds1.

Based on the macrovariables for the output variables, I can't find any difference between variable name from ds1 and name from ds2.

Respected Advisor
Posts: 3,894

Re: DI Studio User Transformation SQL Join

Not sure why you're not using the OOTB SQL Join transformation for this task.

 

As for user written code (with selection: user written body):

PROC SQL ;
  CREATE TABLE &_output. AS
    SELECT ds1.name AS name_1
          ,ds2.name AS name_2
    FROM &_input1 as ds1
    LEFT JOIN &_input2 as ds2 ON ds1.name = ds2.name
  ;
QUIT ;

 

N.B: The order of how you connect your source tables to the User Written Transformation matters. Check the generated code once your code is setup to verify that &_input1/2 point to the right tables.

Occasional Contributor
Posts: 5

Re: DI Studio User Transformation SQL Join

I'm not using the OOTB SQL Join Transformation because I need to do more than just the join and it has to be done several times in one job in dozens of jobs.

Here is a screen of the situation:

screen.PNG

Both input sets have the variable name in it. It is the variable I using to join the two sets. But how do I tell SAS to use variable NAME from the upper dataset (class) and not from the lower?

Here’s the code SAS generates:

%let _INPUT_count = 2; 
%let _INPUT = work.class;
%let _INPUT_connect = ;
%let _INPUT_engine = ;
%let _INPUT_memtype = DATA;
%let _INPUT_options = %nrquote();
%let _INPUT_alter = %nrquote();
%let _INPUT_path = %nrquote(/User Written_A5AYRIWG.C2000GSP%(WorkTable%));
%let _INPUT_type = 1;
%let _INPUT_label = %nrquote();

%let _INPUT0 = work.class;
%let _INPUT0_connect = ;
%let _INPUT0_engine = ;
%let _INPUT0_memtype = DATA;
%let _INPUT0_options = %nrquote();
%let _INPUT0_alter = %nrquote();
%let _INPUT0_path = %nrquote(/User Written_A5AYRIWG.C2000GSP%(WorkTable%));
%let _INPUT0_type = 1;
%let _INPUT0_label = %nrquote();

%let _INPUT1 = work.W47Z4U;
%let _INPUT1_connect = ;
%let _INPUT1_engine = ;
%let _INPUT1_memtype = DATA;
%let _INPUT1_options = %nrquote();
%let _INPUT1_alter = %nrquote();
%let _INPUT1_path = %nrquote(/User Written_A5AYRIWG.C2000GSQ%(WorkTable%));
%let _INPUT1_type = 1;
%let _INPUT1_label = %nrquote();

%let _OUTPUT_count = 1; 
%let _OUTPUT = work.W4YW5C;
%let _OUTPUT_connect = ;
%let _OUTPUT_engine = ;
%let _OUTPUT_memtype = DATA;
%let _OUTPUT_options = %nrquote();
%let _OUTPUT_alter = %nrquote();
%let _OUTPUT_path = %nrquote(/test_OUTPUT0_A5AYRIWG.C2000GSR%(WorkTable%));
%let _OUTPUT_type = 1;
%let _OUTPUT_label = %nrquote();
/* List of target columns to keep  */ 
%let _OUTPUT_keep = name predict;
%let _OUTPUT_col_count = 2;
%let _OUTPUT_col0_name = name;
%let _OUTPUT_col0_table = work.W4YW5C;
%let _OUTPUT_col0_length = 8;
%let _OUTPUT_col0_type = $;
%let _OUTPUT_col0_format = ;
%let _OUTPUT_col0_informat = ;
%let _OUTPUT_col0_label = %nrquote();
%let _OUTPUT_col0_input0 = name;
%let _OUTPUT_col0_exp = ;
%let _OUTPUT_col0_input = name;
%let _OUTPUT_col0_input_count = 1;
%let _OUTPUT_col1_name = predict;
%let _OUTPUT_col1_table = work.W4YW5C;
%let _OUTPUT_col1_length = 8;
%let _OUTPUT_col1_type = ;
%let _OUTPUT_col1_format = ;
%let _OUTPUT_col1_informat = ;
%let _OUTPUT_col1_label = %nrquote();
%let _OUTPUT_col1_input0 = predict;
%let _OUTPUT_col1_exp = ;
%let _OUTPUT_col1_input = predict;
%let _OUTPUT_col1_input_count = 1;

%let _OUTPUT0 = work.W4YW5C;
%let _OUTPUT0_connect = ;
%let _OUTPUT0_engine = ;
%let _OUTPUT0_memtype = DATA;
%let _OUTPUT0_options = %nrquote();
%let _OUTPUT0_alter = %nrquote();
%let _OUTPUT0_path = %nrquote(/test_OUTPUT0_A5AYRIWG.C2000GSR%(WorkTable%));
%let _OUTPUT0_type = 1;
%let _OUTPUT0_label = %nrquote();
/* List of target columns to keep  */ 
%let _OUTPUT0_keep = name predict;
%let _OUTPUT0_col_count = 2;
%let _OUTPUT0_col0_name = name;
%let _OUTPUT0_col0_table = work.W4YW5C;
%let _OUTPUT0_col0_length = 8;
%let _OUTPUT0_col0_type = $;
%let _OUTPUT0_col0_format = ;
%let _OUTPUT0_col0_informat = ;
%let _OUTPUT0_col0_label = %nrquote();
%let _OUTPUT0_col0_input0 = name;
%let _OUTPUT0_col0_exp = ;
%let _OUTPUT0_col0_input = name;
%let _OUTPUT0_col0_input_count = 1;
%let _OUTPUT0_col1_name = predict;
%let _OUTPUT0_col1_table = work.W4YW5C;
%let _OUTPUT0_col1_length = 8;
%let _OUTPUT0_col1_type = ;
%let _OUTPUT0_col1_format = ;
%let _OUTPUT0_col1_informat = ;
%let _OUTPUT0_col1_label = %nrquote();
%let _OUTPUT0_col1_input0 = predict;
%let _OUTPUT0_col1_exp = ;
%let _OUTPUT0_col1_input = predict;
%let _OUTPUT0_col1_input_count = 1;


proc datasets lib=work nolist nowarn memtype = (data view);
   delete W4YW5C;
quit;


/* List of target columns to keep  */ 
%let _keep = name predict;
/* List of target columns to keep  */ 
%let keep = name predict;
* ------------------------------------------------------- ;
* My code comes here ;
* ------------------------------------------------------- ;

%rcSet(&syserr); 
%rcSet(&sysrc); 
%rcSet(&sqlrc); 

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));
%let etls_recnt=-1;



/**  Step end test **/
Super User
Posts: 5,257

Re: DI Studio User Transformation SQL Join

But you have pinpointed a key functionality that DI Studio lacks of, and that I think exists in both MS SSIS and Informatica PowerCenter - the ability to save modified transformation as a template. If it hasn't been done, feel free to enter this as a Ballot Item.

Data never sleeps
Respected Advisor
Posts: 3,894

Re: DI Studio User Transformation SQL Join

@ThierryHerrie

Now I understand what you're asking for. Yes, there is certainly required information missing when selecting "Generate column mapping macros". If one would really want to use this then we'd also need the Libref and Table per source column. This is missing even when using these columns in an expression so you can't implement dynamic code with this insufficient information.

 

Besides of creating a SASWare Ballot idea, I'd also raise this with SAS TechSupport so it gets fed back to SAS R&D.

Super User
Posts: 5,257

Re: DI Studio User Transformation SQL Join

Unfortunately DI Studio doesn't create macro variable for the mapping. In this case also need to enter the source column name into the mapping expression.

If your dozens of jobs have the same structure of data as input you might could use a parameterized job.
Data never sleeps
Ask a Question
Discussion stats
  • 7 replies
  • 242 views
  • 2 likes
  • 4 in conversation