DATA Step, Macro, Functions and more

Reading in text from a file creating one observation from multiple lines in the file

Reply
Learner
Posts: 1

Reading in text from a file creating one observation from multiple lines in the file

Hi,
I have a dilema, I'm trying to create a dataset from a text file but I want to create one observation but get data from multiple lines in the text file. Here is an example. When the line begins with VCR* I want to return the data after, which is a ticket number. Then further down in the list, I want to pull if we sent the revoke comand TRUE or FALSE. The ending data set would have two columns: TICKET_NO      REVOKED
0011730346500 TRUE

****** ****** ****** ****** ******
VCR*0011730346500<
VIRTUAL COUPON RECORD
0011730346500         NAME-

TTL NBR OF CPNS- 2 DATE OF ISSUE-10MAR16 PNR-XXXXXX    10MAR16
FF NBR-AA  44CPH96
CC-AXXXXXXXXXXXX1002
CPN A/L FLT  CLS DATE   BRDOFF  TIME  ST F/B           STAT
 1  AA  8145  N  27MAR  YVRYYZ  1130P OK NA07ZNH5      USED
 2X AA  3783  N  28MAR  YYZPHL  959A  OK NA07ZNH5      OK
FARE CAD    TAX   CA TAX    XG TAX   XT
     TOTAL CAD 
FARE CALC YVR AA X/YTO AA PHLNUCEND ROE XT
          SQUSYCXYXA
FCMI-2
FORM OF PAYMENT
FOP-AXXXXXXXXXXXX1002           EXP-  APPROVAL CODE-
DATE OF ISSUE-10MAR16     ISSUED AT-YYZ    S7S1 A84           #
***** ***** *****
**** Found coupon with OK status: true
**** PNR has been purged: false
*XXXXX*IA<
#NO ITIN#
***** ***** *****
***** Send revoke command: true
VCR#REVOKE-PNR XXXXXX/10-MAR-16 WAS NOT CANCELED PRIOR TO SCHEDU
LED DEPARTURE DATE // TICKET HAS NO VALUE //<
VCR REVOKED
***** ***** *****

Super User
Posts: 5,437

Re: Reading in text from a file creating one observation from multiple lines in the file

One way is to define ticket_no with REATIN. Assign both your variables with substr() or other char function you prefer, based on the automatic _infile_ variable.
Once you assigned revoked, do an explicit output.
Data never sleeps
Super User
Posts: 7,854

Re: Reading in text from a file creating one observation from multiple lines in the file

data have;
infile cards truncover;
input inline $50.;
cards;
****** ****** ****** ****** ******
VCR*0011730346500<
VIRTUAL COUPON RECORD
0011730346500         NAME-
TTL NBR OF CPNS- 2 DATE OF ISSUE-10MAR16 PNR-XXXXXX    10MAR16
FF NBR-AA  44CPH96
CC-AXXXXXXXXXXXX1002
CPN A/L FLT  CLS DATE   BRDOFF  TIME  ST F/B           STAT
 1  AA  8145  N  27MAR  YVRYYZ  1130P OK NA07ZNH5      USED
 2X AA  3783  N  28MAR  YYZPHL  959A  OK NA07ZNH5      OK
FARE CAD    TAX   CA TAX    XG TAX   XT
     TOTAL CAD 
FARE CALC YVR AA X/YTO AA PHLNUCEND ROE XT
          SQUSYCXYXA
FCMI-2
FORM OF PAYMENT
FOP-AXXXXXXXXXXXX1002           EXP-  APPROVAL CODE-
DATE OF ISSUE-10MAR16     ISSUED AT-YYZ    S7S1 A84           #
***** ***** *****
**** Found coupon with OK status: true
**** PNR has been purged: false
*XXXXX*IA<
#NO ITIN#
***** ***** *****
***** Send revoke command: true
VCR#REVOKE-PNR XXXXXX/10-MAR-16 WAS NOT CANCELED PRIOR TO SCHEDU
LED DEPARTURE DATE // TICKET HAS NO VALUE //<
VCR REVOKED
***** ***** *****
;
run;

data want (keep=ticket_no revoked);
set have end=done;
retain
  ticket_no 0
  revoked 'false'
;
if substr(inline,1,4) = 'VCR*'
then ticket_no = input(substr(inline,5,length(inline)-5),best20.);
if substr(inline,1,4) = 'VCR#' and substr(inline,5,10) = 'REVOKE-PNR'
then revoked = 'true';
if done then output;
run;

If you want "revoked" to be truly boolean, set it to 0 in the retain statement and 1 when the right condition is encountered.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 175 views
  • 0 likes
  • 3 in conversation