topic Re: reading pipe delimited file with multiple record layouts in SAS Programming

reading pipe delimited file with multiple record layouts

TAOKES1 — Thu, 23 Aug 2018 13:20:17 GMT

I am new to SAS programming. so what I have is a file using a pipe "|" delimiter, but it has 2 fields that use a secondary delimiter of "?" for data that can contain from 1 to 14 entries for each field. Have it currently set up as:

INFILE INDD1 COL=COL PAD LENGTH=LENGTH MISSOVER DELIMITER='|' dsd;
INPUT @1 LINE $VARYING. LENGTH @;                                 
LENGTH REQTYPE $15 REQUSER $9 REQID $9 PHONE $9 SSN $9 PREFERID $7
FIRST MI LAST BANK CCENTER EMAIL MAILCODE FILL1 FILL2 TASKID $36  
ASSIGNEE HOSTPLAT HOSTFILL PRIOR DATE1 DATE2 ACTION USERID SAPID  
MODELID MODELNM DELDES ADDESS $600;

the fields DELDES & ADDESS are the fields that use the secondary delimiter of ? inside of the.

How do I define and use the individual fields within DELDES & ADDESS ?

Sorry if this is hard to read, I'm an old COBOL developer and this is my first post;

examples of data

example 1:

Byte ----+----10---+----2----+----3----+----4----+----5----+----6
1 |Delete|NODATA|NODATA|1012404|noSSN|BLMORRO|BRITTNEY|X|MORRO
61 W|bank|0004213|BRITTNEY.xxxxxxxxxxxxx.COM|SL-KS-S4FD|||72b02
121 7d9-5e2a-4b2e-8574-8871c2503766|TAOKES1|F400 TOPS PROCESS CO
181 NTROL|F400 TOPS PROCESS CONTROL|priority|TUE AUG 14 18:42:17
241 CDT 2018|TUE AUG 14 18:42:17 CDT 2018|Delete|BLMORRO||||INT
301 ERNAL TABLE: RFS|||||

example 2:

Byte ----+----10---+----2----+----3----+----4----+----5----+----6
1 |Add|NODATA|NODATA|1004604|noSSN|DGTHOM2|DONALD|X|THOMAS|ban
61 k|0005182|DONALD.xxxxxxxxxxxxx.COM|EP-MN-WS4M|||ee497752-8a8
121 2-479a-a206-9395c84b32e5|TAOKES1|F400 TOPS PROCESS CONTROL|F
181 400 TOPS PROCESS CONTROL|priority|TUE AUG 14 11:57:50 CDT 20
241 18|TUE AUG 14 11:57:50 CDT 2018|Add|DGTHOM2|||||OFFICE: OPS|
301 |||

example 3:

Byte ----+----10---+----2----+----3----+----4----+----5----+----6
1 |Change|NODATA|NODATA|1001783|noSSN|TMDOORN|TIMOTHY|X|DOORN|
61 bank|0022410|TIMOTHY.xxxxxxxxxxxK.COM|SL-KS-S1FD|||5e3e7038-
121 b315-4809-8b5c-a03091dbd7e3|TAOKES1|F400 TOPS PROCESS CONTRO
181 L|F400 TOPS PROCESS CONTROL|priority|TUE AUG 14 10:12:53 CDT
241 2018|TUE AUG 14 10:12:53 CDT 2018|Change|TMDOORN||||INTERNA
301 L TABLE: CBO?INTERNAL TABLE: CRV|INTERNAL TABLE: RFS||||

example 4:

Byte ----+----10---+----2----+----3----+----4----+----5----+----6
1 |Add|NODATA|NODATA|993332|noSSN|SLHEND7|SUNNY|X|HENDRIX|bank
61 |0052065|SUNNY.xxxxxxxxxxxxxx.COM|SL-KS-9211|||f5e697ec-61e7
121 -427f-ac00-68e24260e207|TAOKES1|F400 TOPS PROCESS CONTROL|F4
181 00 TOPS PROCESS CONTROL|priority|MON AUG 13 13:43:05 CDT 201
241 8|MON AUG 13 13:43:05 CDT 2018|Add|SLHEND7|||||INTERNAL TABL
301 E: RBS?OFFICE: CMNT||||

When I look at it in excel, this is what I see example data as seen in Excel

Re: reading pipe delimited file with multiple record layouts

Kurt_Bremser — Thu, 23 Aug 2018 12:59:33 GMT

Please stop coding in all-uppercase, it's very hard to read. Especially given that you posted one piece of spaghetti code without any visual separations of functional blocks.

Use the "little running man" symbol for posting code, and show us some examples for the source data. Post that with the {i} button, so that its contents and formatting remain unchanged.

Re: reading pipe delimited file with multiple record layouts

RW9 — Thu, 23 Aug 2018 13:03:26 GMT

Don't write code all in upper case or mixed case, it is very hard to read, and use code window (its the {i} above post area) to post code. I can't see any test data here, so just going to guess. You can have more than one delimiter:

...delimiter='|?'...
input reqtype $ requser $ ...;

With that each part should be read in. It may just be that simple to get it working. If not, post a snippet of the data.

Re: reading pipe delimited file with multiple record layouts

Tom — Thu, 23 Aug 2018 13:27:22 GMT

Just guessing, since you didn't post any sample data, but you probably will want to read those delimited lists into character variables. You can then use the SCAN() function to pick out the individual values within the delimited lists.

infile indd1 dsd dlm='|' truncover;
length reqtype $15 requser $9 reqid $9 phone $9 ssn $9 preferid $7
  first mi last bank ccenter email mailcode fill1 fill2 taskid $36
  assignee hostplat hostfill prior date1 date2 action userid sapid
  modelid modelnm deldes_list addess_list $600
;
input reqtype -- address_list;
array deldes (14)  $100;
array address (14) $100 ;
do  i=1 to dim(deldes);
  deldes(i)=scan(deldes_list,i,'?');
end;
do  i=1 to dim(address);
  address(i)=scan(address_list,i,'?');
end;

Your length statement looks a little funky. Do the all of the last 13 variables really need to be defined with a length of 600?

Looks like you posted some sample records. They all look like they have an empty first field since they all start with a pipe character in the first position. You might need to add another variable to your INPUT statement to account for that.

Re: reading pipe delimited file with multiple record layouts

Ksharp — Thu, 23 Aug 2018 13:32:20 GMT

Check termstr= option of INFILE statement .

And open this file via NotePad++ and click 'show all characters' button at the top of panel.

Re: reading pipe delimited file with multiple record layouts

TAOKES1 — Thu, 23 Aug 2018 13:36:47 GMT

confused with this, I am writing this on mainframe. sorry I am very much an old dinosaur. I am borrowing code and trying to make it fit mine.

Re: reading pipe delimited file with multiple record layouts

TAOKES1 — Thu, 23 Aug 2018 13:37:30 GMT

trying this now, thank you! I have posted data now. I am also adjusting field lengths

Re: reading pipe delimited file with multiple record layouts

Tom — Thu, 23 Aug 2018 13:38:15 GMT

You should not have to worry about TERMSTR on a mainframe. The OS will take care to send SAS the records one by one.

Re: reading pipe delimited file with multiple record layouts

TAOKES1 — Thu, 23 Aug 2018 13:38:18 GMT

thank you, sorry working on mainframe, and old cobol coder, used to doing things in all caps. I have adjusted the info above