topic Re: Row and column delimiter in SAS Programming

Row and column delimiter

Anju — Tue, 29 Nov 2016 21:24:18 GMT

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;

Re: Row and column delimiter

Patrick — Tue, 29 Nov 2016 22:11:51 GMT

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;

Re: Row and column delimiter

Anju — Tue, 29 Nov 2016 22:19:16 GMT

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

Re: Row and column delimiter

Reeza — Tue, 29 Nov 2016 23:29:42 GMT

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.

Re: Row and column delimiter

anoopmohandas7 — Tue, 29 Nov 2016 23:29:51 GMT

Where do you want to delete the row delimiter ?

Re: Row and column delimiter

Patrick — Wed, 30 Nov 2016 00:39:08 GMT

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.

Re: Row and column delimiter

RW9 — Wed, 30 Nov 2016 09:54:28 GMT

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.

Re: Row and column delimiter

Anju — Wed, 30 Nov 2016 18:12:21 GMT

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 !! 🙂

Re: Row and column delimiter

Anju — Wed, 30 Nov 2016 18:14:35 GMT

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 🙂

Re: Row and column delimiter

Qavi — Wed, 28 Jun 2017 12:41:22 GMT

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.

Re: Row and column delimiter

Anju — Wed, 28 Jun 2017 21:54:46 GMT

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.

Re: Row and column delimiter

Anju — Wed, 28 Jun 2017 21:56:26 GMT

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.