DATA Step, Macro, Functions and more

Reading txt file with multiple tables

Reply
Occasional Contributor jo1
Occasional Contributor
Posts: 15

Reading txt file with multiple tables

Hi,

I have been given a txt file that has multiple tables of data in it- each is a matrix of the connectivity of a network. I want to import each table, sum the number of segments each is attached to (to identify which segments are at the top of the network relative to the others) and then append them into a single table.  The number of columns and rows changes in each of the tables (as different parts of the network have varying numbers of segments).  

 

I've done it for a single file (example below):

 

,DBS3B1,30-R12884,30-R68054,30-R1542,30-R1585,30-R1587,30-R1540,30-R1541,30-R188,30-R336,30-R68935,30-R2882,30-R2570,30-R3197,30-R3008,30-R13213
DBS3B1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
30-R12884,,1,,,,,,,,,,,,,,
30-R68054,,,1,1,,,,,,,,,,,,
30-R1542,,,,1,,,,,,,,,,,,
30-R1585,,,,,1,1,1,1,,,,,,,,
30-R1587,,,,,,1,1,1,,,,,,,,
30-R1540,,,,,,,1,,,,,,,,,
30-R1541,,,,,,,,1,,,,,,,,
30-R188,,,,,,,,,1,1,1,1,1,1,1,1
30-R336,,,,,,,,,,1,1,,,,,
30-R68935,,,,,,,,,,,1,,,,,
30-R2882,,,,,,,,,,,,1,1,1,1,
30-R2570,,,,,,,,,,,,,1,,,
30-R3197,,,,,,,,,,,,,,1,,
30-R3008,,,,,,,,,,,,,,,1,
30-R13213,,,,,,,,,,,,,,,,1

 

Part of the OUTPUT:

DBS3B1 16
30-R12884  1
30-R13213  1
30-R1540  1
30-R1541  1
30-R1542  1
30-R1585  4
30-R1587  3
30-R188  8

The issue that I'm having is that they have given me a single file with a matrix for each feeder of the network  - here is an example of the format showing 2 feeders (tables) - the first row of the new table is ,CBP142 33kV,30-R3893,30-F3890 - can someone given me a start on how to get SAS to read this and identify that there are 2 tables, my actual file has hundreds of matrixs so I can't do it manually.

 

,CBP142 22kV,30-R11970,30-R12880,30-FL62656,30-F3945
CBP142 22kV,1,1,1,1,1
30-R11970,,1,1,1,
30-R12880,,,1,,
30-FL62656,,,,1,
30-F3945,,,,,1
,CBP142 33kV,30-R3893,30-F3890
CBP142 33kV,1,1,1
30-R3893,,1,1
30-F3890,,,1

 

I'm using SAS Enterprise Guide 7.1

Super User
Posts: 24,012

Re: Reading txt file with multiple tables

I don't think you need to do that. SAS has BY group processing so you can read it all into one data set and keep the 'groups' multiple tables in one data set but uniquely identified. Or at least give you a starting point to create your output. 

 

While reading a file, you can get the length of the record. It seems the first line, the length is typically longer so if you read the line, check the length then you can make a judgement of whether this is the start or end of a file. 

 

I would suggest providing a sample text file with at least two tables and showing the exact expected output from those files. 

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 90 views
  • 0 likes
  • 2 in conversation