Remove single quote before to import data

ropjunior — Mon, 22 Aug 2022 13:05:03 GMT

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

Re: Remove single quote before to import data

Tom — Mon, 22 Aug 2022 13:19:54 GMT

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:

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

Re: Remove single quote before to import data

ropjunior — Mon, 22 Aug 2022 14:27:00 GMT

Thank You so much

topic Re: Remove single quote before to import data in New SAS User

Remove single quote before to import data

Re: Remove single quote before to import data

Re: Remove single quote before to import data