topic SAS DI Studio : Append many tables into one Without dragging them from the Folders section in SAS Data Management

SAS DI Studio : Append many tables into one Without dragging them from the Folders section

Zeus_Olympus — Tue, 21 Jun 2022 08:08:59 GMT

Hello,

I am fairly new to SAS DI Studio. I would like to append many tables together into one without dragging each one of them onto APPEND transformation on the Job canvas.

I'd rather prefer a kind of "User written code" as a Job, the datasets concatenations being based on certain prefix of the datasets names.

Thanking you in advance for your support.

Re: SAS DI Studio : Append many tables into one Without dragging them from the Folders section

Patrick — Tue, 21 Jun 2022 08:37:39 GMT

Also what you're asking for could be done via user written code the DI way of doing things is using table metadata objects. That's what gives you the lineage on SAS metadata level.

I can understand that you don't want n-append nodes. As an alternative what could work is using the SQL SET transformation. This should allow for a single node with all your source tables as input and a single target table as output. Just make sure that you configure things so it generates SQL union corr all code.

With user written code and to still have some lineage on metadata level:

You could connect all input table metadata to a user written code node and then use the generated macro variables with the table names in your user written code. This would then still support lineage on metadata table level (but no more on column level).

The "sad story": If your physical source tables follow a naming pattern then it would be very easy to implement using SAS syntax only without any metadata involved. But it's then no more the DIS way and breaks metadata lineage and though a decision to be made with "open eyes".

Re: SAS DI Studio : Append many tables into one Without dragging them from the Folders section

LinusH — Tue, 21 Jun 2022 09:52:30 GMT

If you have a fixed no of inputs, I would do the drag-and-drop stuff, just to keep it graphical, and have the lineage intact as @Patrick mentions.

However, if your sources are "identical" with perhaps a date, region or product differentiator, you could use a loop transform, and then use a macro variable in the input table(s) physical name. Less no of objects, and potentially you can run those appends in parallel if your target table engine permits (RDBMS, SPDS).

Re: SAS DI Studio : Append many tables into one Without dragging them from the Folders section

ErikLund_Jensen — Tue, 21 Jun 2022 18:14:53 GMT

Hi @Zeus_Olympus

Since you want an alternative to dragging all the tables to the job canvas, i suppose that the datasets are registered in metadata. If that is the case, and the tables resides in the same library, there is a simple solution based on the use of one of the tables to represent them all.

It is not best practice, and it is dangerous too. Without the lineage your input datasets have no impact, and somebody (like me) might decide to clean them out as obsolete. I will not recommend it, but I am not laying down policies for your site, so here we go:

First step is to create the job. Drag one table to the canvas, which one doesn't matter as long as it resides in the right library, because it is used only to get the libname assigned in the job. The table name including the libref is then accessible for the user written code in the macro variable &_INPUT, and library information including data sets is also added to the SASHELP views.

Second: Write the code for the User Written transformation. It uses the libref to access the SASHELP.VMEMBER view and get a list of dataset names with a given prefix supplied in the code. Upcase because all information in the sashelp-tables is in uppercase.

%let dsnprefix = compare;

proc sql noprint;
	select catx('.',scan("&_INPUT",1,'.'),memname) into :memnamelist separated by ' '
	from sashelp.vmember
	where upcase(scan("&_INPUT",1,'.')) = libname and memname EQT upcase("&dsnprefix");
quit;

data &_OUTPUT;
	set &memnamelist;
run;

It makes sense only if all input datasets have the same - or almost the same - structure. Variable lengths are taken from the first occurrence of any given variable, so if a following dataset have a longer variable with the same name, it will be truncated. Variables with the same name must also be of the same type, i.e. char or num.

Short, effecient, but definitely cutting corners.

Re: SAS DI Studio : Append many tables into one Without dragging them from the Folders section

Zeus_Olympus — Fri, 24 Jun 2022 07:12:33 GMT

Dear Erik,

I tried your suggestion and I get the beloww errors:

=================================================

WARNING: Apparent symbolic reference _OUTPUT not resolved.
117
118 data &_OUTPUT;
_
22
200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

119 set &memnamelist;
_
22
200
WARNING: Apparent symbolic reference MEMNAMELIST not resolved.
ERROR: File WORK.MEMNAMELIST.DATA does not exist.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS,
NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

===================================================

I can't figure it out.

Additionally I would like to add a couple of criteria in selecting the tables to be further concatenated into one e.g.

... and memname like 'CV_%'
or memname like 'SD_%'
or memname like 'MN_%'

Thanking you in advance

Re: SAS DI Studio : Append many tables into one Without dragging them from the Folders section

Patrick — Fri, 24 Jun 2022 08:38:33 GMT

@Zeus_Olympus There is somewhere in the transformation a checkbox where you can select for the transformation to generate macro variables.

It's also really always worth to look at the generated code as this tells you what's available.

If you're using using written code then go for "user written body" ...or how that's called. I remember there are two options for user written code - choose the one that still generates the header and footer macros.

Re: SAS DI Studio : Append many tables into one Without dragging them from the Folders section

ErikLund_Jensen — Sat, 25 Jun 2022 15:24:58 GMT

Hi @Zeus_Olympus

I am sorry to hear that.

It all depends on one table coupled ti the transformation as input.

First: Check that you have selected "User written body". As &_OUTPUT is undefined one suspects that you have choosen All user written

After that you should see these macro variables in the code pane:

The last part is to modify the code to select more than one prefix. The simplest solution is to drop the dsnprefix macro variable and use constants in the select, something like this, where you can add as many as necessary, as long as they are 3 chars long to match the substring:

proc sql noprint;
	select catx('.',scan("&_INPUT",1,'.'),memname) into :memnamelist separated by ' '
	from sashelp.vmember
	where upcase(scan("&_INPUT",1,'.')) = libname and substr(memname,1,3) in ('CV_', 'SD_', 'MN_');
quit;

data &_OUTPUT;
	set &memnamelist;
run;

I hope this solves your problem.

Re: SAS DI Studio : Append many tables into one Without dragging them from the Folders section

Zeus_Olympus — Mon, 27 Jun 2022 10:42:20 GMT

Thank you very much, ErikLund !!!

I still need quite a lot of work to start feeling comfortable with DI.

Υour clear and detailed help gives me a good boost.