Row and column delimiter

[Anju]

I have my data in the text file which looks like below. My column delimeter = !~~! and my row delimeter = ~##~. When I run my below code the row delimiter is read as column, It doesn't identify my column delimeter and read the next varaible as a new row. Can someone let me know how to read the row delimiter here ? Thanks for your help.

00064342424242!~~!N/A!~~!test_mail@GMAIL.COM!~~!!~~!!~~!!~~!06/01/2017 00:00:00.000!~~!!~~!!~~!!~~!!~~!!~~!!~~!!~~!~##~
907992146412508561EMPEA!~~!N/A!~~!EWS1001@SEP16.COM!~~!!~~!!~~!!~~!06/01/2017 00:00:00.000!~~!!~~!!~~!!~~!!~~!!~~!!~~!!~~!~##~
00024242424242!~~!N/A!~~!abc@gmail.com!~~!!~~!!~~!!~~!06/01/2017 00:00:00.000!~~!!~~!!~~!!~~!!~~!!~~!!~~!!~~!~##~

data account;
infile 'text_location.TXT' DLMSTR ='!~~!' dsd flowover ;
input col_1 :$50.
col_2 :$50.
col_3 :$255.
col_4 :$255.
col_5 :$255.
col_6 :$255.
col_7 :$255.
col_8 :$50.
col_9 :$19.
col_10 :$50.
col_11 :$50.
col_12 :$50.
col_13 :$50.
col_14 :$1.
col_15 :$1.
col_16 :$25.
col_17 :$50.
col_18 :$50.
col_19 :$50.
col_20 :$9.
col_21 :$1.
col_22 :$1.
col23 :$19.
col_24 :$19.
col_25 :$17.
col_26 :$255.
col_27 :$255.
col_28 :$255.
col_29 :$255.
col_30 :$255.
col_31 :$255.
col_32 :$255.
col_33 :$255.
col_34 :$50.
col_35 :$50.
col_36 :$50.
col_37 :$50.
col_38 :$50.;
run;

[Patrick]

If I copy the data you've posted then they appear to be on a single line and every record has the same number of fields and delimiters. If this is also the case with your real data then:

1. only read as many columns as you actually need/have in your file

2. use Truncover instead of Flowover.

filename txt temp;
data _null_;
  file txt;
  infile datalines;
  input;
  put _infile_;
  datalines;
00064342424242!~~!N/A!~~!test_mail@GMAIL.COM!~~!!~~!!~~!!~~!06/01/2017 00:00:00.000!~~!!~~!!~~!!~~!!~~!!~~!!~~!!~~!~##~
907992146412508561EMPEA!~~!N/A!~~!EWS1001@SEP16.COM!~~!!~~!!~~!!~~!06/01/2017 00:00:00.000!~~!!~~!!~~!!~~!!~~!!~~!!~~!!~~!~##~
00024242424242!~~!N/A!~~!abc@gmail.com!~~!!~~!!~~!!~~!06/01/2017 00:00:00.000!~~!!~~!!~~!!~~!!~~!!~~!!~~!!~~!~##~
;
run;
 
data account;
  /*infile 'text_location.TXT' DLMSTR ='!~~!' dsd flowover ;*/
  infile txt DLMSTR ='!~~!' dsd truncover ;
  attrib 
    col_1 - col_14 informat=$50.
    ;
  input col_1 - col_14;
run;

[Anju]

Thanks for the quick reply. Sure, Is there a way I can delete the row delimiter here ?

[Reeza · Accepted solution]

Personally, I would read it in as a variable and then drop it, ie Col15. 

SAS doesn't have a way of identifying row delimiters that jumps to mind.

[anoopmohandas7]

Where do you want to delete the row delimiter ?

[Patrick]

If your data is as posted then the actual line delimiter SAS uses to determine "end of line" is still a LineFeed (LF) - or CRLF if it's Windows.

The row delimiter you're talking about is for SAS just another field - and as it's the last field in the record just don't map it to a SAS column. That's what I've done in the sample code posted.

Should your actual data be on a single line coming in as a data stream without any end of line indicators like CRLF and the only way how you can determine a new record is this row delimiter string, then let us know.

[Anju]

Patrick,

I only read the columns i am interested in and ignored the last one which is also a row delimiter. The flowover with DLMSTR worked. Thanks for your help on this !! 🙂

[Anju]

Yes thats what I did. I was wondering if SAS might have a row delimiter. But I did not read that column so I am good now 🙂

[Qavi]

Hi Patrick,

I am trying to read the data from .txt external file through SAS Code,it has '|' as a column delimiter and '<' as a row delimiter.I am not able to read the data in proper way,I am using below mentioned code to read the data from the server.Data is coming in as a data stream without any end of line indicators like CRLF.I tried all the options FLOWOVER,MISSOVER and TRUCOVER but i am not getting the desired result.Can you please let me know how to read the row delimiter here ? Thanks for your help.

DATA WORK.PURCHASE;

    LENGTH

        Filer_TIN        $ 11

        Ret_Type         $ 1

        Period_Key         8

        TAXABLE_GOODS_VAL   8

        AMT_TAX_VAL        8

        INCL_TAX_VAL       8

        COMP_VAL           8

        TAX_FRE_SALES_VAL   8

        EXEM_SALES_VAL     8

        LABOUR_CHRGES_VAL   8

        OTHER_CHRGES_VAL   8

        GROSS_TOTAL_VAL    8

        FORM_NUM           8

        TYPE_OF_TX         8

        Partner_TIN      $ 11;

    FORMAT

        Filer_TIN        $CHAR11.

        Ret_Type         $CHAR1.

        Period_Key       BEST4.

        TAXABLE_GOODS_VAL BEST11.

        AMT_TAX_VAL      BEST9.

        INCL_TAX_VAL     BEST10.

        COMP_VAL         BEST9.

        TAX_FRE_SALES_VAL BEST10.

        EXEM_SALES_VAL   BEST8.

        LABOUR_CHRGES_VAL BEST10.

        OTHER_CHRGES_VAL BEST9.

        GROSS_TOTAL_VAL  BEST11.

        FORM_NUM         BEST3.

        TYPE_OF_TX       BEST2.

        Partner_TIN      $CHAR11.;

    INFORMAT

        Filer_TIN        $CHAR11.

        Ret_Type         $CHAR1.

        Period_Key       BEST4.

        TAXABLE_GOODS_VAL BEST11.

        AMT_TAX_VAL      BEST9.

        INCL_TAX_VAL     BEST10.

        COMP_VAL         BEST9.

        TAX_FRE_SALES_VAL BEST10.

        EXEM_SALES_VAL   BEST8.

        LABOUR_CHRGES_VAL BEST10.

        OTHER_CHRGES_VAL BEST9.

        GROSS_TOTAL_VAL  BEST11.

        FORM_NUM         BEST3.

        TYPE_OF_TX       BEST2.

        Partner_TIN      $CHAR11.;

    INFILE '/sas/EIUData/PURCHASE_ANNEX.txt'

        LRECL=102

        ENCODING="UTF-8"

DLMSTR="|" truncover;

    INPUT

        Filer_TIN        : $CHAR11.

        Ret_Type         : $CHAR1.

        Period_Key       : ?? BEST4.

        TAXABLE_GOODS_VAL : ?? COMMA11.

        AMT_TAX_VAL      : ?? COMMA9.

        INCL_TAX_VAL     : ?? COMMA10.

        COMP_VAL         : ?? COMMA9.

        TAX_FRE_SALES_VAL : ?? COMMA10.

        EXEM_SALES_VAL   : ?? COMMA8.

        LABOUR_CHRGES_VAL : ?? COMMA10.

        OTHER_CHRGES_VAL : ?? COMMA9.

        GROSS_TOTAL_VAL  : ?? COMMA11.

        FORM_NUM         : ?? BEST3.

        TYPE_OF_TX       : ?? BEST2.

        Partner_TIN      : $CHAR11. ;

RUN;

How we can read "<" as rew delimitre in SAS?coud you please help me on this?

Sample Data :

27020018076 |R|1606|5045.48|277.48|0.00|0.00|0.00|0.00|0.00|0.00|5322.96|231 |10 |27080058880 |<27020018076 |R|1606|947.87|52.13|0.00|0.00|0.00|0.00|
0.00|0.00|1000.00|231 |10 |27131130266 |<27020018076 |R|1606|1848.34|101.66|0.00|0.00|0.00|0.00|0.00|0.00|1950.00|231 |10 |27220036105
|<27020018076 |R|1606|8597.17|472.85|0.00|0.00|0.00|0.00|0.00|0.00|9070.02|231 |10 |27220646138 |<27020018076 |R|1606|22800.00|1360.00|0.00|
0.00|0.00|0.00|0.00|0.00|24160.00|231 |10 |27240041138 |<27020018076 |R|1606|67096.22|4025.02|0.00|0.00|0.00|0.00|0.00|0.00|71121.24|231 |10
|27260645728 |<27020018076 |R|1606|6493.12|357.12|0.00|0.00|0.00|0.00|0.00|0.00|6850.24|231 |10 |27270301033 |<27020018076 |R|1606|945001.00|
52605.00|0.00|0.00|0.00|0.00|0.00|0.00|997606.00|231 |10 |27290000710 |<27020018076 |R|1606|3981.04|218.96|0.00|0.00|0.00|0.00|0.00|0.00|4200.00|
231 |10 |27290279682 |<27020018076 |R|1606|2800.00|168.00|0.00|0.00|0.00|0.00|0.00|0.00|2968.00|231 |10 |27361171219 |<27020018076 |R|1606|
59052.49|3247.87|0.00|0.00|0.00|0.00|0.00|0.00|62300.36|231 |10 |27390019279 |<27020018076 |R|1606|8583163.22|514989.78|0.00|0.00|0.00|0.00|
0.00|0.00|9098153.00|231 |10 |27460293314 |<27020018076 |R|1606|440662.27|26439.73|0.00|0.00|0.00|0.00|0.00|0.00|467102.00|231 |10 |
27490371140 |<27020018076 |R|1606|4146.96|228.08|0.00|0.00|0.00|0.00|0.00|0.00|4375.04|231 |10 |27530294100 |<27020018076 |R|1606|54424.24|
7347.24|0.00|0.00|0.00|0.00|0.00|0.00|61771.48|231 |10 |27610060189 |<27020018076 |R|1606|3500.50|192.50|0.00|0.00|0.00|0.00|0.00|0.00|3693.00|
231 |10 |27650269493 |<27020018076 |R|1606|471985.10|28319.10|0.00|0.00|0.00|0.00|0.00|0.00|500304.20|231 |10 |27670990871 |<27020018076 |
R|1606|2853.81|171.23|0.00|0.00|0.00|0.00|0.00|0.00|3025.04|231 |10 |27690241729 |<27020018076 |R|1606|642012.00|38970.74|0.00|0.00|0.00|0.00|

Kindly find the attached sample data for your refrence.

[RW9]

Where is the file from, can you change it?  I would suggest using a standard file format - CSV, XML, Json.  Creating your own special file format just means more work from both ends, and no-one else will be able to pick it up an run.  For instance, why not just use default line endings - even if the system is different that can be handled easily with an option. 

[Anju]

Hi,

Read the last row as another extra column and drop it.

If the row delimeter appears in your last column you can use something like if col50='~##~' then col50=' ';

Hope this helps. 

[Anju]

Hi,

Read the last row as another extra column and drop it.

If the row delimeter appears in your last column you can use something like if col50='~##~' then col50=' ';

Else you can use compress function to remove the value '~##~'  from col50

Hope this helps.