Need to read a text file using a layout in a dataset

Accepted Solution Solved
Reply
Occasional Contributor jtb
Occasional Contributor
Posts: 6
Accepted Solution

Need to read a text file using a layout in a dataset


I have a layout that was sent to me in .csv form.  There are over 2000 fields on this file.

I also have the file that matches the layout.

I would like to read in the file without typing in the layout.  Is there a way to read in the layout and then use it to read in the file?

Thanks!

JTB.


Accepted Solutions
Solution
‎04-09-2013 01:37 PM
Trusted Advisor
Posts: 2,113

Re: Need to read a text file using a layout in a dataset

What I have typically done in this case is to write a DATA step to read the .csv file and generate the SAS code to read the actual data.  Then I use %INCLUDE to build the subsequent DATA step to read the actual data.

View solution in original post


All Replies
Respected Advisor
Posts: 3,777

Re: Need to read a text file using a layout in a dataset

What does the file that matches the layout contain?  Can you attach it.

Occasional Contributor jtb
Occasional Contributor
Posts: 6

Re: Need to read a text file using a layout in a dataset

The layout is basically as follows:

field name1, start position1, end postion1, length1, char/numric1

field name2, start position2, end postion2, length2, char/numric2

etc.

the file is a text, fixed field file that would match.

I was going to just read everything in as character, so I wouldn't have to worry about that.  And we wanted to use it multiple times, so we were going to set it up as a macro.  I know I could read the layout in, print it and then copy/paste into a SAS program, but we were hoping to make it completely automated.

Super User
Posts: 10,466

Re: Need to read a text file using a layout in a dataset

You may have some issues with the field name as provided. You will need to insure that the field names are valid SAS variable names. The NVALID function is your friend here. If you have any that wouldn't be valid for SAS variable names you will want to create a map from the original to a valid SAS name. I would add a SAS variable name field to the data descriptor set if any are needed, typically replacing spaces and invalid characters with underscore. If you have fields whose names are identical up to the maximum length of a SAS variable name you'll have to work something else out.

I would also look to see if you have values that should be Date or Datetime variables as you may want to read them in as such using an informat and assign likely formats. For example you may receive a CHAR variable that looks like 10/27/2009 or 27OCT2009 that may be better as a SAS date for reporting or manipulation.

After looking at that and deciding on name and formats then a data _null_ step can write out the data step as Doc suggests.

Respected Advisor
Posts: 3,777

Re: Need to read a text file using a layout in a dataset

Sounds fishy why would you need start and stop position for a CSV.

Occasional Contributor jtb
Occasional Contributor
Posts: 6

Re: Need to read a text file using a layout in a dataset

I don't need a start and stop for the .csv file.  The .csv file is the layout for the fixed field text file.

So I read in the layout (.csv file) as a delimited file.

The contents of this file contains field names, and positional data for the fixed field file.

Solution
‎04-09-2013 01:37 PM
Trusted Advisor
Posts: 2,113

Re: Need to read a text file using a layout in a dataset

What I have typically done in this case is to write a DATA step to read the .csv file and generate the SAS code to read the actual data.  Then I use %INCLUDE to build the subsequent DATA step to read the actual data.

Occasional Contributor jtb
Occasional Contributor
Posts: 6

Re: Need to read a text file using a layout in a dataset

So, read in the layout.  Print statements to a text file that reads in the fixed field file.  Use %include on the text file?  Hmmm... Ok.  That could work.  Thank you!

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 240 views
  • 4 likes
  • 4 in conversation