Dynamic column names to be read as proc import in DI studio

Reply
Contributor
Posts: 62

Dynamic column names to be read as proc import in DI studio

Hi ,

My requirement is to read a basic text file with delimited inputs where the first column has column names in SAS DI.

I can use proc import in Base sas, but is there any transformation or procedure to implement proc import code in SAS DI without using user written code.

Ex:

name|age|sex

kumar|24|m

sasi|25|m

alice|29|m

next time the data may come like

age|name|sex

24|m|kumar

25|sasi|m

29|alice|m

Note here delimeter is pipe symbol and data may also differ.

Thank s in advance

Regards,

Imroze

Esteemed Advisor
Esteemed Advisor
Posts: 7,263

Re: Dynamic column names to be read as proc import in DI studio

Hi,

Sorry, you seem to be asking "is there a funtion which will import any data in any format without me having to do anything".  This is not possible.  You should have, per good transfer standards an agreement of what data, in what format is to be received (Import Agreement?).  From that you developing an import process.  If your data source is changing as columns mixing up, differing delimiter etc. how would you expect to write a program?  If its just the order of variables (which shouldn't be the case if you have an import procedure) then its possible, read the file using datastep and only use the first line to generate the list of variables to be read in.  However I still feel your problem lies in the lack of import agreement between vendor and recipient and any solution you may come up with will just change each time.

Contributor
Posts: 62

Re: Dynamic column names to be read as proc import in DI studio

Thank you for your reply RW9.

Here the data delimeter is fixed say pipe symbol, but the columns are differing (dynamically changing) . I have tried reading the column names from the first row, but for the second time it is giving an error as the column names are different.

Request you to please let me know is there any way to read dynamic column names in SAS DI apart from using User written code.

Regards,

Imroze

Esteemed Advisor
Esteemed Advisor
Posts: 7,263

Re: Dynamic column names to be read as proc import in DI studio

You shouldn't need a dynamic name reader.  Use proc import:

proc import datafile="xyz.txt" out=mydata dlm="|";

     getnames="yes";

run;

This should create a dataset for each of your given examples with columns mixed up, which shouldn't affect further processing.

Contributor
Posts: 62

Re: Dynamic column names to be read as proc import in DI studio

May I know how to write proc import code with out using user written in SAS DI please.

SAS Employee
Posts: 340

Re: Dynamic column names to be read as proc import in DI studio

Why do you want to stay away from  user written code?

Is it because this type of import occures often in your DIS jobs, and you don't want developers to write SAS code all the time?

If this is the case, you can create a custom transformation. This requires SAS code writing, but when it's ready and tested, DIS developers can re-use it whenever they want. It is like using any other built-in transformation.

Contributor
Posts: 62

Re: Dynamic column names to be read as proc import in DI studio

Hi Gergely,

Thank you for the reply, client has solution for that (they used some where in there previous implementation, without using userwritten) it seems. But I don't want to ask them :-( . so thought I will try my best without using user written.

Regards,

Imroze

Esteemed Advisor
Posts: 5,202

Re: Dynamic column names to be read as proc import in DI studio

I can sympathize with the idea of not using User Written. In my experience, SAS programmers that become DI developers tend to use User Written too often, some people all the time.

But, DI Studio can not cover all types of ETL cases out of the box, hence the possibility of using User Written code.

But then I agree with @KurtBremser, dynamic file layouts and SAS metadata is a terrible marriage.

You have to ask yourself, is this source data an ETL task?

Have you done the required columns level mapping from source data to a properly modeled data warehouse/mart?

If not, this could perhaps be som kind of data that you should dump on a file share, Hadoop or even load in Visual Analytics for manual analysis/ad-hoc reporting.

Once proper transformations rules/mapping have been conducted, leave it out of your DW/DI environment.

Data never sleeps
Esteemed Advisor
Posts: 6,735

Re: Dynamic column names to be read as proc import in DI studio

SAS DI creates the necessary code for import from metadata definitions about the external infile(s).

Now I'm pretty sure that there is some "clever" (clever in the sense that Python programmers use) way to manipulate metadata for external files automatically and then recreate the DI jobs, but I consider this a VERY BAD IDEA.

The whole process is meant to create a stable platform; undermining that torpedoes the whole idea.

So, if you want to be totally flexible, forget using DI studio and get aquainted with the idea of permanently changing code which YOU have to maintain all on your own. And which will not be following principles of accountability so often required by law.

Or get the morons to deliver data in an orderly way.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 8 replies
  • 398 views
  • 0 likes
  • 5 in conversation