topic How to put filename into the data using SAS-DI? in SAS Data Management

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

suttipop — Mon, 17 Jul 2017 06:23:53 GMT

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?

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

Kurt_Bremser — Mon, 17 Jul 2017 06:34:13 GMT

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.

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

LinusH — Mon, 17 Jul 2017 15:19:18 GMT

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?

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

LinusH — Mon, 17 Jul 2017 15:27:05 GMT

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

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

LinusH — Tue, 18 Jul 2017 06:22:32 GMT

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.

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

Patrick — Tue, 19 Sep 2017 11:48:26 GMT

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/397083#M12052 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.