Does SAS have a macro to format output of user writen DI transformation?

Question

When developing ETL programs with SAS Data Integration (DI) Studio, each transformation you specify has a neath user interface to specify the columns of the datasets(=tables) you create and their type, length and format.

When the existing transformations can not do the job and jou need user written code, you should do as much as possible with the point-and-click interface because

it enables impact analysis and reverse impact analysis
your successors will probably not dive into the SAS base code unless they realy need to
if they ever need to change the columns or their format, they expect changing the specifications in the user interface will do the job

Fortunately SAS discloses the format of the output to the programmer of a user written transformation in macro variables

So I often write my code inside the user written transformation as

%macro format_output(data);
    %do _col = 0 %to  &&&data._col_count - 1; 
        length &&&data._col&_col._name &&&data._col&_col._type&&&data._col&_col._length;
        %if %length(&&&data._col&_col._format) %then %do;
            format &&&data._col&_col._name &&&data._col&_col._format;
        %end;
    %end;
%mend;

data &_OUTPUT1 (keep=&_OUTPUT1_keep);   
    %format_output(_OUTPUT1);
    set &_INPUT1;

    * actua code ;
run;

Does SAS supply an embedded macro or something to do the same thing?

For completenes: the way SAS discloses the structure of the output looks like

%let _OUTPUT1 = myLib.myTable;
%let _OUTPUT1_connect = ;
%let _OUTPUT1_engine = ;
%let _OUTPUT1_memtype = DATA;
%let _OUTPUT1_options = %nrquote();
%let _OUTPUT1_alter = %nrquote();
%let _OUTPUT1_path = %nrquote(/myTable_A5E5JYT6.C7000MOL%(WorkTable%));
%let _OUTPUT1_type = 1;
%let _OUTPUT1_label = %nrquote();
/* List of target columns to keep  */ 
%let _OUTPUT1_keep = myID first_item <16 more items> last_item;
%let _OUTPUT1_col_count = 19;
%let _OUTPUT1_col0_name = myID;
%let _OUTPUT1_col0_table = myLib.myTable;
%let _OUTPUT1_col0_length = 8;
%let _OUTPUT1_col0_type = ;
%let _OUTPUT1_col0_format = 13.;
%let _OUTPUT1_col0_informat = 13.;
%let _OUTPUT1_col0_label = %nrquote(my ID);
%let _OUTPUT1_col0_input0 = myID;
%let _OUTPUT1_col0_exp = ;
%let _OUTPUT1_col0_input = myID;
%let _OUTPUT1_col0_input_count = 1;
%let _OUTPUT1_col1_name = first_item;
%let _OUTPUT1_col1_table = myLib.myTable;
%let _OUTPUT1_col1_length = 8;
%let _OUTPUT1_col1_type = $;
%let _OUTPUT1_col1_format = $CHAR8.;
%let _OUTPUT1_col1_informat = $CHAR8.;
%let _OUTPUT1_col1_label = %nrquote(first data item in my table);
%let _OUTPUT1_col1_input0 = first_item;
%let _OUTPUT1_col1_exp = ;
%let _OUTPUT1_col1_input = first_item;
%let _OUTPUT1_col1_input_count = 1;

<documentation about 16 more columns>

%let _OUTPUT1_col18_name = last_item;
%let _OUTPUT1_col18_table = myLib.myTable;
%let _OUTPUT1_col18_length = 16;
%let _OUTPUT1_col18_type = $;
%let _OUTPUT1_col18_format = $16.;
%let _OUTPUT1_col18_informat = $16.;
%let _OUTPUT1_col18_label = %nrquote(last data item in my table);
%let _OUTPUT1_col18_input0 = last_item;
%let _OUTPUT1_col18_exp = ;
%let _OUTPUT1_col18_input = last_item;
%let _OUTPUT1_col18_input_count = 1;
%let _OUTPUT1_filetype = WorkTable;

Which version of SAS and DI Studio are you using? – momo1644 Mar 23 '18 at 11:38 — momo1644, Mar 23 '18 at 11:38
Version=4.901 Operating system=Windows 7 6.1 – Dirk Horsten Mar 23 '18 at 11:51 — Dirk Horsten, Mar 23 '18 at 11:51

momo1644 · Answer 1 · 2018-03-23T13:23:34.187

-1

In SAS DI Studio, The user written nodes can have more than one output table visualy visible & accessible from the node icon, and by right clicking on the table you can see change the table details (table name, type, length, format, informat, .. etc ). Please note I am using version 4.6

In my Screenshot below my user written node already has three output table. to add a forth one: Right Click on the node select "Add Work Table" A new window will open for you to enter the table detail

In the DI Auto generated code for your node, sas will assign _OUTPUT1,2,3,4 to your tables, so in your code you can reference them with their corresponding &Output

Once you added your tables, you can see and edit all the output table details (length, format, informat) by selecting their properties from the visual interface (point and click). So you wont need a macro for that.

edited Mar 23 '18 at 13:23

answered Mar 23 '18 at 11:50

momo1644

1,769
9
25

This is obviously not the Solution for the given problem, since all your approach provides are macrovariables derived from the information provided by the visual Interface. If you don't use this macrovariables in your macrocode they have literally zero effect. – Michael Kersten Mar 06 '19 at 15:09
@MichaelKersten Can you please provide your recommendation / solution? thanks – ofzy Jun 07 '21 at 12:18
@ofzy There are two possible approaches to the given problem. 1. In case you have access to a SAS DI Studio installation you could register the tables you need macro variables as described and create a dummy-job consisting of only a empty user written transformation with the newly registered table as output and just copy the code which is then automaticly generated. But thats not how I understood the original question, which asked explicit for a macro or built in SAS (Base) function. Which leads to approach 2. – Michael Kersten Jun 07 '21 at 19:07
Approach 2 would be to generate the needed macro variables based on the SAS system view VCOLUMN found in the library SASHELP. This view contains all column informations for all datasets known in a given SAS session (all libs are included). With a datastep using CALL SYMPUT you can generate the wished macro variables. This approach was not known to me when I wrote my comment 2 years ago :) – Michael Kersten Jun 07 '21 at 19:18

Does SAS have a macro to format output of user writen DI transformation?

Does SAS supply an embedded macro or something to do the same thing?

1 Answers1