DATA Step, Macro, Functions and more

Row and column delimiter

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Row and column delimiter

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;

 

 


Accepted Solutions
Solution
‎11-30-2016 01:14 PM
Super User
Posts: 17,750

Re: Row and column delimiter

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.

View solution in original post


All Replies
Respected Advisor
Posts: 3,887

Re: Row and column delimiter

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;
Occasional Contributor
Posts: 7

Re: Row and column delimiter

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

Solution
‎11-30-2016 01:14 PM
Super User
Posts: 17,750

Re: Row and column delimiter

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.

Contributor
Posts: 44

Re: Row and column delimiter

Where do you want to delete the row delimiter ?

Respected Advisor
Posts: 3,887

Re: Row and column delimiter

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.

Occasional Contributor
Posts: 7

Re: Row and column delimiter

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 !! Smiley Happy

Occasional Contributor
Posts: 7

Re: Row and column delimiter

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 Smiley Happy

New Contributor
Posts: 4

Re: Row and column delimiter

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.
Super User
Super User
Posts: 7,392

Re: Row and column delimiter

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. 

Occasional Contributor
Posts: 7

Re: Row and column delimiter

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. 

Occasional Contributor
Posts: 7

Re: Row and column delimiter

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. 

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 339 views
  • 0 likes
  • 6 in conversation