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?
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.
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.
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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.