how to import tsv file

[Jarvin99]

Dear all,

I really encounter this error:

ERROR: Invalid file, D:\Dropbox\Dataset\PatentsView\patent.tsv.

My code is:

data pav.patent ;
	%let _EFIERR_ = 0; /*set the ERROR detection macro variable*/
	infile 'D:\Dropbox\Dataset\PatentsView\patent.tsv' delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2;
	informat id 8.;
 	informat type $100.;
 	informat number 8.;
 	informat country $20.;
 	informat date $12.;
 	informat abstract $1100.;
 	informat title $200.;
 	informat kind $10.;
 	informat num_claims 8.;
	informat filename $200.;
 	informat withdrawn 8.;
	format id 8.;
 	format type $100.;
 	format number 8.;
 	format country $20.;
 	format date $12.;
 	format abstract $1100.;
 	format title $200.;
 	format kind $10.;
 	format num_claims 8.;
	format filename $200.;
 	format withdrawn 8.;
;
  input id -- withdrawn ;
run;

I do not know where goes wrong. I download this tsv file from the "patent" file via this link: https://patentsview.org/download/data-download-tables.

Thank you.

[Reeza · Accepted solution]

filename fPatent url 'D:\Dropbox\Dataset\PatentsView\patent.tsv';


data pav.patent ;
	%let _EFIERR_ = 0; /*set the ERROR detection macro variable*/
	infile FPATENT delimiter='09'x TRUNCOVER DSD lrecl=32767 firstobs=2;
	informat id 8.;
 	informat type $100.;
 	informat number 8.;
 	informat country $20.;
 	informat date $12.;
 	informat abstract $1100.;
 	informat title $200.;
 	informat kind $10.;
 	informat num_claims 8.;
	informat filename $200.;
 	informat withdrawn 8.;
	format id 8.;
 	format type $100.;
 	format number 8.;
 	format country $20.;
 	format date $12.;
 	format abstract $1100.;
 	format title $200.;
 	format kind $10.;
 	format num_claims 8.;
	format filename $200.;
 	format withdrawn 8.;
;
  input id -- withdrawn ;
run;

---

@Jarvin99 wrote:

Dear all,

I really encounter this error:

ERROR: Invalid file, D:\Dropbox\Dataset\PatentsView\patent.tsv.

My code is:

data pav.patent ;
	%let _EFIERR_ = 0; /*set the ERROR detection macro variable*/
	infile 'D:\Dropbox\Dataset\PatentsView\patent.tsv' delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2;
	informat id 8.;
 	informat type $100.;
 	informat number 8.;
 	informat country $20.;
 	informat date $12.;
 	informat abstract $1100.;
 	informat title $200.;
 	informat kind $10.;
 	informat num_claims 8.;
	informat filename $200.;
 	informat withdrawn 8.;
	format id 8.;
 	format type $100.;
 	format number 8.;
 	format country $20.;
 	format date $12.;
 	format abstract $1100.;
 	format title $200.;
 	format kind $10.;
 	format num_claims 8.;
	format filename $200.;
 	format withdrawn 8.;
;
  input id -- withdrawn ;
run;

I do not know where goes wrong. I download this tsv file from the "patent" file via this link: https://patentsview.org/download/data-download-tables.

Thank you.

---

Try using the approach above, creating a filename URL reference ahead of the import.

[jklaverstijn]

Well it works for me. I downloaded the zipped version of the file, unzipped it and ran your code successfully against it. At least the first 1000 rows as it is quite big.

Something else is the matter here. Try to open the file with your favourite text editor. Maybe that will give you some clue. Maybe you lack access rights, the unzip went wrong, the file is still in zipped format, ... So many things that can be at play here.

Regards,

- Jan

[Reeza]

Show the log

[Kurt_Bremser]

95% that 😧 is a local drive on your PC, while your SAS session runs on a remote server. If that is the case, upload the file to the server and read it from there. The details depend on the interface you use (Enterprise Guide or SAS Studio).

[Tom]

Please show the actual log.  

Most likely the path you used does not work on the machine where SAS is actually running.  Make sure you have copied the file to that machine and you can point SAS at the file.

Other issues you will have.

1) Some of the lines in the file are longer than the default 32,767 bytes.  Set a longer LRECL= option on the INFILE statement.

2) You are truncating the ABSTRACT field.  In fact some of the values are longer than the maximum 32,767 bytes that SAS can store in one variable.

3) Two of the lines in the file have embedded linefeeds that will cause SAS to treat the line as two lines.  You might want to use something like this to fix the file before trying to read it with SAS.  https://github.com/sasutils/macros/blob/master/replace_crlf.sas