Exclude a string trailer record when the field it resides is a numeric field

Reply
Occasional Contributor Roc
Occasional Contributor
Posts: 16

Exclude a string trailer record when the field it resides is a numeric field

Team,

 

We had to add trailer records to all our souce files we load with Data intergration. The trailer is always in the first column. In most cases this isa charater field and we can use Substring SUBSTR(DEA_NUMBER,1,4)  ^= 'TR~~' but some trailers fields are numberic and this dose not work. I cannot find and cast function or one to evaluate the data type. Can anyone help here?

 

Thanks ahead all 

Grand Advisor
Posts: 10,194

Re: Exclude a string trailer record when the field it resides is a numeric field

We may need more information but if I understand what your are looking for then you would SUBSTR a Put value:

 

substr( put(numericvalue, best12.), 1,4)

 

or similar. possible issues for what you actually want would be where a decimal may crop up. Without specific examples of input and desired results a bit hard to be more specific.

Occasional Contributor Roc
Occasional Contributor
Posts: 16

Re: Exclude a string trailer record when the field it resides is a numeric field

[ Edited ]

The trailer Record looks like 

25633699

23657855

"TR~~P~~YYYYMMDD~~HHMMSS~~RECORD_COUNT"   <-- field is Numberic this is a string obviously. 

We need it not to load that record. We thried the above in the where and a record still loaded into results.

 

Thanks.

Grand Advisor
Posts: 10,194

Re: Exclude a string trailer record when the field it resides is a numeric field

Try

if anyalpha(x)>0;

in the code reading the file where X is name of the variable you are reading that contains the possibly objectionable all digit values.

 

You may need to provide more details of the structure and contents of the input file.

Occasional Contributor Roc
Occasional Contributor
Posts: 16

Re: Exclude a string trailer record when the field it resides is a numeric field

[ Edited ]

Thanks for the replies but those only work on a column that is defined as character. My column is numeric and the trailer record is character "FTR*******************. It never loads the trailer value but it loads empty into all other values. Basically what I need is a function that looks at a number and asks is it a number true or false. 

 

Thanks Ahead all. 

 

If I could evaluate the 1st number as 1 or 2 or 4 might work but not sure i can extract the 1st number.

 

Grand Advisor
Posts: 10,194

Re: Exclude a string trailer record when the field it resides is a numeric field

Show some data.

If the colum is numeric it cannot contain "FTR*******************" if it is in a SAS data set. It would be missing.

Are you attempting to READ a file into SAS?

If so then what is the structure of the file being read.

 

Valued Guide
Posts: 763

Re: Exclude a string trailer record when the field it resides is a numeric field

Hi.  Just use a CAT function within the SUBSTR function (works with CHAR and NUM variables) ...


data x;
input x y;
if substr(cat(x),1,4) eq '9999';
datalines;
99992345 1224
12347890 7865
89999999 1234
;

 

the LOG (no errors, no warnings) ......
6   data x;
17   input x y;
18   if substr(cat(x),1,4) eq '9999';
19   datalines;

NOTE: The data set WORK.X has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):

 

Ask a Question
Discussion stats
  • 6 replies
  • 352 views
  • 0 likes
  • 3 in conversation