BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ropjunior
Fluorite | Level 6

Hello,

 

I'm having problems to import a file with single quotes, can i remove that character before to import?

 

follow data

TIPO_MVTO;NUM_SINI_TRON;NUM_SINI;NUM_EXP;NUM_MVTO_ECO;NUM_APOLICE_TRON;NUM_APOLICE;NUM_ENDOSSO
MVTO TECNICO;'10011722000507;'10011722000507;1;5;';'100000926018;10
MVTO TECNICO;'10011722000507;'10011722000507;1;5;';'100000926018;10

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You need to explain how you want to parse those lines.

Your header line makes it look like there are 8 values per line.

If I just use the semicolon as delimiters with the DSD option then the field with only the single quote looks like it is ending the quoted value started by the earlier field that started with a single quote.  So there are only 5 values on the line, not 8.

1 MVTO TECNICO
2 '10011722000507
3 '10011722000507;1;5;'
4 '100000926018
5 10

But if you read it without the DSD option then SAS will treat it as 8 fields.

First let's create a file with the lines you posted.

filename ssv temp;
options parmcards=ssv;
parcards4;
TIPO_MVTO;NUM_SINI_TRON;NUM_SINI;NUM_EXP;NUM_MVTO_ECO;NUM_APOLICE_TRON;NUM_APOLICE;NUM_ENDOSSO
MVTO TECNICO;'10011722000507;'10011722000507;1;5;';'100000926018;10
MVTO TECNICO;'10011722000507;'10011722000507;1;5;';'100000926018;10
;;;;

Now let's read it with a simple data step.

data want;
  infile ssv dlm=';' truncover firstobs=2;
  input TIPO_MVTO :$20. NUM_SINI_TRON :$20. NUM_SINI :$20. 
        NUM_EXP NUM_MVTO_ECO 
        NUM_APOLICE_TRON :$20. NUM_APOLICE :$20. NUM_ENDOSSO 
  ;
run;

Result:

Tom_0-1661174239062.png

 

If the lines are shorter than 32,767 bytes you can modify the _INFILE_ variable to remove the single quotes.  Then you could use the DSD option.

data want;
  infile ssv dsd dlm=';' truncover firstobs=2;
  input @;
  _infile_=compress(_infile_,"'");
  input TIPO_MVTO :$20. NUM_SINI_TRON :$20. NUM_SINI :$20. 
        NUM_EXP NUM_MVTO_ECO 
        NUM_APOLICE_TRON :$20. NUM_APOLICE :$20. NUM_ENDOSSO 
  ;
run;

Results

Tom_1-1661174383126.png

 

 

 

 

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

You need to explain how you want to parse those lines.

Your header line makes it look like there are 8 values per line.

If I just use the semicolon as delimiters with the DSD option then the field with only the single quote looks like it is ending the quoted value started by the earlier field that started with a single quote.  So there are only 5 values on the line, not 8.

1 MVTO TECNICO
2 '10011722000507
3 '10011722000507;1;5;'
4 '100000926018
5 10

But if you read it without the DSD option then SAS will treat it as 8 fields.

First let's create a file with the lines you posted.

filename ssv temp;
options parmcards=ssv;
parcards4;
TIPO_MVTO;NUM_SINI_TRON;NUM_SINI;NUM_EXP;NUM_MVTO_ECO;NUM_APOLICE_TRON;NUM_APOLICE;NUM_ENDOSSO
MVTO TECNICO;'10011722000507;'10011722000507;1;5;';'100000926018;10
MVTO TECNICO;'10011722000507;'10011722000507;1;5;';'100000926018;10
;;;;

Now let's read it with a simple data step.

data want;
  infile ssv dlm=';' truncover firstobs=2;
  input TIPO_MVTO :$20. NUM_SINI_TRON :$20. NUM_SINI :$20. 
        NUM_EXP NUM_MVTO_ECO 
        NUM_APOLICE_TRON :$20. NUM_APOLICE :$20. NUM_ENDOSSO 
  ;
run;

Result:

Tom_0-1661174239062.png

 

If the lines are shorter than 32,767 bytes you can modify the _INFILE_ variable to remove the single quotes.  Then you could use the DSD option.

data want;
  infile ssv dsd dlm=';' truncover firstobs=2;
  input @;
  _infile_=compress(_infile_,"'");
  input TIPO_MVTO :$20. NUM_SINI_TRON :$20. NUM_SINI :$20. 
        NUM_EXP NUM_MVTO_ECO 
        NUM_APOLICE_TRON :$20. NUM_APOLICE :$20. NUM_ENDOSSO 
  ;
run;

Results

Tom_1-1661174383126.png

 

 

 

 

ropjunior
Fluorite | Level 6

Thank You so much

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 755 views
  • 3 likes
  • 2 in conversation