topic How do I create a text file that doesn't import with "Lost Card" warning? in SAS Programming

How do I create a text file that doesn't import with "Lost Card" warning?

Ryanb2 — Thu, 18 May 2017 16:30:00 GMT

I wrote code that reads in fixed-width text files then looks for specific lines and writes them to a dataset. Then I export lines from that dataset into a new text file that combines all of the lines I was looking for into one file. My goal was to keep the lines exactly as they were in the text files from which they originate such that the import code will import the new text file exactly as it would the original files. When I try to import the text file I get a "Lost Card" note (see log text below). Also it only imports half of the records. There should be 1445 imported and it only imports 722. Otherwise the lines import correctly.

I think I'm not exporting the file correctly. I used some code from a similar project I worked on to create a csv file and that seemed to work great. I tried changing the "mod=" option to different numbers without success. I looked at other "Lost Card" posts and there have been suggestions such as using truncover on the import. However, because this file is going to passed to someone else and imported into a non-SAS system, I need the new text file to be exactly like the original text files. The purpose of importing the new text file into SAS is only a test to see if it the formats are the same. 'The import code works fine for the originals leading me to think there are differences.

1) Am I not exporting my file correctly?

2) Is there a way to edit text files without importing them into SAS and storing them in a SAS dataset? Even though I tried to minimize the possibility of unforeseen changes, it seems the act of importing and storing the lines in SAS modifies them to some extent.

Thanks,

Ryan

52015 /* Import TRX file*/
52016 %let filename_1=temp.TRX;
52017 %import_TRX_TRP(C:\Users\Public\&filename_1,test);

NOTE: The infile IMPORT is:
Filename=C:\Users\Public\temp.TRX,
RECFM=V,LRECL=32767,File Size (bytes)=566440,
Last Modified=17May2017:15:42:55,
Create Time=17May2017:15:24:42

NOTE: LOST CARD.
t_death_year=2017 ...<DELETED SENSITIVE INFORMATION HERE> priority=. _ERROR_=1 _N_=723
NOTE: 1445 records were read from the infile IMPORT.
The minimum record length was 390.
The maximum record length was 390.
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.TEST has 722 observations and 108 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

/*****************************************************************************************************
Collect all records that need to be imported
*****************************************************************************************************/
proc sql noprint;  /* Create macro variables holding directories, filenames, and extensions of all trp and trx files */
	select distinct(final_path)
		into :path separated by '@'
		from CCDF_TRX_TRP
		where final_path ne '';
quit;
%let path_obs=&sqlobs;
%put &path_obs &path;

%macro catch_up();
	%do i = 1 %to &path_obs;  /* iterate through the list of full_paths */

		%let path_itr= %scan(&path, &i, '@');  /* scans through the list of files */

		proc sql noprint;  /* Create list of SFNs needed for each of the files */
		   select distinct catt("'", SFN_1, "'")
		      into :SFN_list separated by ' '
		      from CCDF_TRX_TRP
			  where final_path eq "&path_itr";
		quit;

		data comprehensive_temp; 
			missing;

		  	infile "&path_itr" lrecl=2000 truncover;

			length lines $2000; 
		  	input lines $1-2000; 
			keep lines ; 

			if '305'||substr(lines,1,4)||substr(lines,7,6) in (&SFN_list);
		run;

		data comprehensive;
			%if &i = 1 %then %do; set comprehensive_temp; %end;
			%else %do; set comprehensive comprehensive_temp; %end;
			if lines ne '';
		run;
	%end;
%mend catch_up;
%catch_up();


/*****************************************************************************************************
Export cath_up file to I drive
*****************************************************************************************************/
Options noQuoteLenMax;  /* Supresses the warning "WARNING: The quoted string currently being processed has become more than 262 characters long" */
data _null_;
	set comprehensive;
	file "C:\Users\Public\temp.TRX" lrecl=2000 n=/*4*/1 MOD;  * Export file to temporary location;
	put lines;
run;

/* Import TRX file*/
%let filename_1=temp.TRX;
%import_TRX_TRP(C:\Users\Public\&filename_1,test);

Re: How do I create a text file that doesn't import with "Lost Card" warning?

ballardw — Thu, 18 May 2017 16:55:57 GMT

When you use this:

input lines $1-2000;

It forces SAS to attempt to read 2000 columns but your diagnostics show that the longest record is 390 (in one example) and tries to read the next bit.

Here's one way:

data want;
   infile "yourfile" lrecl=2000;
   length lines $ 2000;
   input ;
   lines=_infile_;
run;

_infile_ is an automatic variable containing the entire line of the file when Input executes.

It depends on what you mean by "edit". You can actually have a FILE statement for an output file in the same data step and use PUT to write there. The reads from one file, puts to another without creating a data set.

data _null_;
   infile "yourfile" lrecl=2000;
   File "your output file" ;
   length lines $ 2000;
   input ;
   lines=_infile_;
   put lines;
run;

Put can be conditional so you could use a line like:

if '305'||substr(lines,1,4)||substr(lines,7,6) in (&SFN_list) then Put lines;

Re: How do I create a text file that doesn't import with "Lost Card" warning?

Ryanb2 — Thu, 18 May 2017 16:57:21 GMT

Brilliant! Works perfectly. Thanks for your help.

Re: How do I create a text file that doesn't import with "Lost Card" warning?

Ryanb2 — Thu, 18 May 2017 17:58:07 GMT

Thanks for the reply ballardw. I thought it worked because I didn't get the lost card error and I had the correct number of records; however, I realized that when I ran the export code the second time it stacked the lines on top of the previous output duplicating the records that were already there. What's interesting is the end result of importing this duplicated file is the dataset I was looking to import (i.e., all records with no dupes). When I delete the text file and run the code again I still ge the "Lost Card" note as well as only half of the records from the text file.

I also tried your second solution, but I get the same "Lost Card" note, and the file resulting from the put statement does not include all 1445 records (Strangely it includes 1397 records).

Any other thoughts?

Re: How do I create a text file that doesn't import with "Lost Card" warning?

Ryanb2 — Thu, 18 May 2017 18:09:05 GMT

The code for the second solution results in 1397 records instead of 1445 because it's only outputing records from one input file. Effectively overwriting the first input file with the second rather than stacking them on top of each other.

Re: How do I create a text file that doesn't import with "Lost Card" warning?

Ryanb2 — Thu, 18 May 2017 19:12:25 GMT

It looks like the line from the original text file is 407 characters long because there are 17 spaces(?) added to the end. The spaces are removed in the new text file. I tried adding spaces back several different ways but no matter what I try the spaces are stripped at the end of the line in the new text file.

It seems like the import code does not recognize the end of the line and that's why it's only importing every other line. I tried to add carriage returns and line feeds but they seem to be stripped as well. If I add another put statement to create a blank line, the file imports correctly, but that's not the format of the original file.

Does anyone know how to retain the spaces or add spaces and/or line feeds and carriage returns.

Re: How do I create a text file that doesn't import with "Lost Card" warning?

Ryanb2 — Thu, 18 May 2017 19:56:02 GMT

I made some modifications based on the code ballardw provided and by adding the spaces to the end of the put statement made the import work successfully. Thanks.

%LET run_started = %SYSFUNC( DATETIME(), DATETIME ) ;      /* Assigns date and time to macro variable run_started  */
%LET run_started = %SYSFUNC(tranwrd(&run_started,:,-) ) ;  /* replaces colon with dash so that it can be included in filenames */

proc sql noprint;  /* Create macro variables holding directories, filenames, and extensions of all trp and trx files */
	select distinct(final_path)
		into :path separated by '@'
		from CCDF_TRX_TRP
		where final_path ne '';
quit;
%let path_obs=&sqlobs;
%put &path_obs &path;

%macro catch_up();
	%do i = 1 %to &path_obs;  /* iterate through the list of full_paths */

		%let path_itr= %scan(&path, &i, '@');  /* scans through the list of files */

		proc sql noprint;  /* Create list of SFNs needed for each of the files */
		   select distinct catt("'", SFN_1, "'")
		      into :SFN_list separated by ' '
		      from CCDF_TRX_TRP
			  where final_path eq "&path_itr";
		quit;

		data _null_; 
			missing;

		  	infile "&path_itr" lrecl=407 truncover;
			File "C:\Users\Public\catch_up_file_&run_started..TRX" mod; /* mod tells SAS to stack new records rather than write-over */

			length lines $407; 
		  	input lines $1-407;

			if '305'||substr(lines,1,4)||substr(lines,7,6) in (&SFN_list) then put lines '                ';  /* Add back truncated spaces at end of line - needed for import */
		run;
	%end;
%mend catch_up;
%catch_up();

Re: How do I create a text file that doesn't import with "Lost Card" warning?

ballardw — Thu, 18 May 2017 20:34:17 GMT

If the issue is writing a fixed lenght padded with blanks use the proper options:

data _null_;
  file "yourfilename.txt" lrecl=80 pad;
  x= "Short text";
  put x;
run;

the output file will have blanks to a length of 80.

@Ryanb2 wrote:

It seems like the import code does not recognize the end of the line and that's why it's only importing every other line.

IF you use a specified format on an input that exceeds the length of the actual input line then you read past the end of line in effect eating the following line. There are many interactions between how you specify what to read and the file options such as PAD TRUNCOVER MISSOVER FLOWOVER RECFMT DELIMITER TERMSTR LRECL. Without example input data and the exact desired output it can be a bit hard to catch all of the needed options.

@And with Put you get to consider use of column/line pointers (@n or #n), trailing @ or @@;

You may need to describe more about what you mean by add "line feeds or carriage controls". Which operating system you operate under comes into consideration.

A bare Put; will right a newline to the output destination in the form of the current operating system. If you need a character that is NOT your current OS line ender then you can use the BYTE function to assign the value of any chacter to a varaible and put that character but likely getting into more than you actually need.