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
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.
This worked perfectly, thanks for your help!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.