Help with Input on formated input

Reply
New Contributor
Posts: 3

Help with Input on formated input

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

Attachment
Super User
Posts: 10,500

Re: Help with Input on formated input

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.

New Contributor
Posts: 3

Re: Help with Input on formated input

This worked perfectly, thanks for your help!

Super User
Posts: 10,500

Re: Help with Input on formated input

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.

Ask a Question
Discussion stats
  • 3 replies
  • 266 views
  • 1 like
  • 2 in conversation