topic Re: Exclude a string trailer record when the field it resides is a numeric field in SAS Programming

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

Roc — Wed, 28 Oct 2015 17:28:40 GMT

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

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

ballardw — Wed, 28 Oct 2015 17:46:51 GMT

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.

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

Roc — Wed, 28 Oct 2015 18:52:44 GMT

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.

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

ballardw — Wed, 28 Oct 2015 23:30:03 GMT

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.

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

Roc — Thu, 29 Oct 2015 03:19:55 GMT

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.

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

MikeZdeb — Thu, 29 Oct 2015 12:36:38 GMT

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):

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

ballardw — Thu, 29 Oct 2015 15:08:04 GMT

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.