BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anju
Calcite | Level 5

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

11 REPLIES 11
Patrick
Opal | Level 21

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
Calcite | Level 5

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

Reeza
Super User

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
Quartz | Level 8

Where do you want to delete the row delimiter ?

Patrick
Opal | Level 21

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
Calcite | Level 5

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
Calcite | Level 5

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Calcite | Level 5

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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