SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

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

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.

 

SAS.PNG

 

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

 

SAS4.PNG 

 

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

 

SAS2.PNG

 

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

 

SAS3.PNG

 

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


Accepted Solutions
Solution
‎07-23-2017 09:36 PM
Respected Advisor
Posts: 4,173

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

[ Edited ]

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.

Capture.JPG

Capture.JPG

 

 

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

View solution in original post


All Replies
Super User
Posts: 7,832

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,434

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

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?
Data never sleeps
Super User
Posts: 5,434

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

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...
Data never sleeps
Super User
Posts: 5,434

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

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.
Data never sleeps
Solution
‎07-23-2017 09:36 PM
Respected Advisor
Posts: 4,173

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

[ Edited ]

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.

Capture.JPG

Capture.JPG

 

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 683 views
  • 4 likes
  • 4 in conversation