How to put filename into the data using SAS-DI?

[suttipop]

I'm a new SAS's developer. I'm using SAS-DI to create a job. I need to put filename into a column of data because I create a job that read multiple input files and write it into a output file. The filename column will help to trackback to input file.

I try to create a external file connect to file reader box. It look like below.

In external file (INPUT FILE), I put additional options to keep filename into "infilename" variable.

 

In File Reader box, I put "infilename" variable into expression at the filename column.

Running is pass but It error when I try to open output of File Reader box. The error message is "Unable to execute query: SQL passthru expression contained these errors: ERROR: The following columns were not found in the contributing tables: infilename."

Please advice me, How to put filename into the data using SAS-DI

Please advice me, How to put filename into the data using SAS-DI?

[Kurt_Bremser]

The automatically created variable from the filename= option cannot be included in the dataset. You have to assign its value to another variable in the data step.

Since your question concerns DI Studio, I will move your post to the proper Data Management community.

[LinusH]

It's not a ready made check box for this, but it can be accomplished in several ways.
How so you generate the file name, and what transformation(s) do you use?

[LinusH]

Sorry, didn't see the attached images.
Your problem is that the mapping occurs in a sub sequent SQL step, and I guess your INFILE optiona is not preserved there. Check the generated code to understand how things are executing.

"FILENAME= variable is not written to the data set."

I need to get home to my own SAS DIS for experimenting a bit...

[LinusH]

I didn't have time to experiment. But one dirty trick would be to add an assignment statement in the INFILE options box, so the filename variable is preserved.
But still wonder how you set the physical file name in the first place. There are more elegant solutions if you have it as a parameter to the job, and a loop job in case of multiple files.

[Patrick · Accepted solution]

Added on 19 September 2017 by the author of below post:

Below solution actually doesn't work because DIS will generate the length definition for _infilename AFTER variable _infilename has been used in filename=_infilename. Because of this sequence _infilename will actually get assigned a default and too short length. The SAS log will show the following Warning

WARNING: Length of character variable _infilename has already been set. 
         Use the LENGTH statement as the very first statement in the DATA STEP to declare the length of a character variable.

Refer to https://communities.sas.com/t5/SAS-Data-Management/Adding-Multiple-Filenames-to-DI-Studio-Job/m-p/39... for solution options.

@suttipop

It's always worth to look into the generated code to understand why things are not working - and to get ideas what you could try to make it work.

As @LinusH was hinting there is a "hack" option to make this work.

Once you've done such a set-up look into the generated code to understand how this affected code generation.