topic Re: Read Value with inverted comma in SAS Programming

Read Value with inverted comma

atul_desh — Mon, 26 Jun 2017 13:50:53 GMT

I've want to read var2 & var8 with (") in it.

I think dsd considering inverted comma as delimeter.

how can I read value ? please help !!

data ds2;
infile datalines dsd dlm="|" missover;
input (var1-var8) ($);
datalines;
2|"28 |4|||||29" |
run;

proc print data=ds2;
run;

Re: Read Value with inverted comma

Ksharp — Mon, 26 Jun 2017 14:12:58 GMT

data ds2;
infile datalines ;
input;
var1=scan(_infile_,1,'|','m');
var2=scan(_infile_,2,'|','m');
var3=scan(_infile_,3,'|','m');
var4=scan(_infile_,4,'|','m');
var5=scan(_infile_,5,'|','m');
var6=scan(_infile_,6,'|','m');
var7=scan(_infile_,7,'|','m');
var8=scan(_infile_,8,'|','m');

datalines;
2|"28 |4|||||29" |
run;

proc print data=ds2;
run;

Re: Read Value with inverted comma

ballardw — Mon, 26 Jun 2017 14:13:38 GMT

If your data does not contain any Pipe characters as part of the actual data then you probably do not need DSD to read it.

If you data is not pipe delimited then you should indicate how the data is actually structured.

Re: Read Value with inverted comma

Kurt_Bremser — Mon, 26 Jun 2017 14:16:20 GMT

@ballardw the problem comes from the dual function of dsd; one needs dsd so that two or more successive delimiters are read as missing values. But then SAS also drops the quotes.

Re: Read Value with inverted comma

atul_desh — Mon, 26 Jun 2017 14:18:30 GMT

My data is in tab delimited text file... but I shown here as pipe delimited just to more readable.

Re: Read Value with inverted comma

art297 — Mon, 26 Jun 2017 14:19:35 GMT

The following should work:

data ds2;
  infile datalines dsd dlm="|" missover;
  input @;
  _infile_=translate(_infile_,'~','"');
  input (var1-var8) ($);
  array have(*) $ var1-var8;
  do i=1 to dim(have);
    have(i)=translate(have(i),'"','~');
  end;
datalines;
2|"28 |4|||||29" |
run;

Art, CEO, AnalystFinder.com

Re: Read Value with inverted comma

atul_desh — Mon, 26 Jun 2017 14:28:05 GMT

thank you but Actual data which I'm having is having 180 variable and 85 million observation.. so using array make them more difficult to run...longer to run.

Re: Read Value with inverted comma

art297 — Mon, 26 Jun 2017 14:33:23 GMT

Do you need to keep the quotation marks? If not, the array isn't needed as they could just be compressed rather than converted to a tilde.

Art, CEO, AnalystFinder.com

Re: Read Value with inverted comma

atul_desh — Mon, 26 Jun 2017 15:02:25 GMT

Yes I do need quotation mark !! 😞

Re: Read Value with inverted comma

art297 — Mon, 26 Jun 2017 17:50:53 GMT

Then you could use:

data ds2;
 infile datalines dlm="|" missover;
 input @;
 do while (index(_infile_,'||') gt 0);
 _infile_=tranwrd(_infile_,"||","|.|");
 end;
 input (var1-var8) ($);
datalines;
2|"28 |4|||||29" |
;
run;

Art, CEO, AnalystFinder.com

Re: Read Value with inverted comma

atul_desh — Mon, 26 Jun 2017 15:34:43 GMT

In Actaul file it is tab delimeted... I'm trying to replace | with '09'x..

Re: Read Value with inverted comma

atul_desh — Mon, 26 Jun 2017 15:50:25 GMT

yup.. is there any way so that dsd don't consider double quotes & single quotes as delimiter and only consider Pipe or '09'x ??

Re: Read Value with inverted comma

art297 — Mon, 26 Jun 2017 17:50:01 GMT

My original code had an uncorrected oversight which I'll correct after posting this. The following will work with your tab delimited file:

data ds2;
  infile datalines dlm='09'x missover;
  input @;
  do while (index(_infile_,'0909'x) gt 0);
    _infile_=tranwrd(_infile_,'0909'x,'092E09'x);
  end;
  input (var1-var8) ($);
  datalines;
2	"28 	4					29"	
;
run;

Art, CEO, AnalystFinder.com

Re: Read Value with inverted comma

atul_desh — Mon, 10 Jul 2017 15:42:11 GMT

@art297 I'm getting issue when my first column is blank...even though i've tried missover, truncover but issue still persist.

Re: Read Value with inverted comma

Tom — Mon, 10 Jul 2017 15:56:34 GMT

@atul_desh wrote:

@art297 I'm getting issue when my first column is blank...even though i've tried missover, truncover but issue still persist.

Add code to handle that case.

if _infile_=: '09'x then _infile_='.'||_infile_;

Re: Read Value with inverted comma

art297 — Mon, 10 Jul 2017 16:00:23 GMT

I agree with @Tom:

data ds2;
  infile datalines dlm='09'x missover;
  input @;
  do while (index(_infile_,'0909'x) gt 0);
    _infile_=tranwrd(_infile_,'0909'x,'092E09'x);
  end;
  if _infile_=: '09'x then _infile_='.'||_infile_;  input (var1-var8) ($);
  datalines;
2	"28 	4					29"	
	"28 	4					29"	
2	"28 	4					29"	
;
run;

Art, CEO, AnalystFinder.com

Re: Read Value with inverted comma

atul_desh — Tue, 11 Jul 2017 08:49:02 GMT

Thank you so much @art297and @Tom