Help with Input on formated input

BrandonSanford — Mon, 16 Jul 2012 19:55:06 GMT

I am stumped and would appreciate some help. I am trying to input a fixed column report (headers repeat every page) with some non standard data (see sample text file). The problem I am having is that the values for any column between Begin_Bal and Ending_balance could be a positive or a negative with a trailing negative sign. I.E 51-

On every column except the ending_balance column using the TRAILSGN10. works correctly. On the ending_balance field it will either import the data as positve values only or only import values with a trailing negative. I have tried bringing the field in as a text field without luck. As soon as I expand the lenght of the field from 11 to 12 all of my positve values are returned as missing/blank.

I have several dozen large files that need to be imported and running the process twice once for positive values and again for negative values is not an optimal solution. Any suggestions/assistance would be greately appreciated. Please note that the Points to Expire field on the sample file is has no values but is a repeating header row in the file.

Example code

data work.Results;

infile ‘C:\Sample data.txt'

Firstobs=11 /*Excludes header rows*/

missover;

Input @1 Acct_Num $16.

@18 ST $1.

@20 PT $1.

@26 BEGIN_BAL TRAILSGN10.

@36 PTS_EARNED TRAILSGN10.

@45 PROMO_POINTS TRAILSGN10.

@55 SCOREMORE_PTS TRAILSGN10.

@67 RELATIONSHIP_PTS_EARNED TRAILSGN10.

@79 POINTS_ADJUSTED TRAILSGN10.

@90 POINTS_REDEEMED TRAILSGN10.

@100 Points_Expired TRAILSGN10.

@110 ENDING_BALANCE TRAILSGN11.;

run;

Desired results:

Acct Num ST PT Begin_Bal Pts_earned Promo_Points ScoreMore_points Relationship_pts_earned Points_adjusted Points_Redeemed Points_expired Ending_Balance

XXXX1546 A Y 500 100 50 650

XXXX9246 A Y -51 -51

XXXX2741 A Y 1000 -2500 500 -1000

Re: Help with Input on formated input

ballardw — Mon, 16 Jul 2012 22:08:33 GMT

Your example data has the - sign for ending balance in column 121; column 110 to 121 is 12 columns so you need TRAILSIGN12.

Also the MISSOVER is dropping your values because the lines with out the trailing negative are too short. Change to TRUNCOVER instead.

Re: Help with Input on formated input

BrandonSanford — Tue, 17 Jul 2012 13:00:00 GMT

This worked perfectly, thanks for your help!

Re: Help with Input on formated input

ballardw — Tue, 17 Jul 2012 14:53:58 GMT

Another option would be to specify on the infile statement a value of LRECL greater than the longest line you expect and add the PAD option. This would make the lines long enough to SAS that the TRUNCOVER isn't needed.

topic Re: Help with Input on formated input in SAS Programming

Help with Input on formated input

Re: Help with Input on formated input

Re: Help with Input on formated input

Re: Help with Input on formated input