I am importing data from a report in text format using the data import wizard.
The bulk of the data witihn the body of the report is being brought in as observations.
However I need to locate three peices of infromation from the header.
The header information needs to be repeated in variables for each observation.
RUN DATE : 05/13/17 TIME : 08:57:55 PAGE 324
REPORT #: 044W-001 PROGRAM: 030
AUTOMATED CHECK OFFSET ACTIVITY REPORT
BANK NUMBER: 001 ACCOUNT NUMBER: 0000010xxxxxxxx
ACCT REQ REF PROVIDER/ INITIAL OFFSET REISSUE WRITE-OFF PENALTY CHECK CHECK AGREEMENT REF
I can locate the data in the header and can create a table recording the header data and each observation; but I would like to repeat the header data for each observation where REF_STAT = ACT.
REF_STAT ACCNT_NUM REPORT_DATE REPORT_TIME
05/13/17 08:57:55
0000010xxxxxxxx
ACT
ACT
ACT
Something like this in a program entry? :
data test;
length REF_STAT ACCNT_NUM_tmp REPORT_DATE_tmp REPORT_TIME_tmp ACCNT_NUM REPORT_DATE REPORT_TIME $32;
retain REF_STAT ACCNT_NUM REPORT_DATE REPORT_TIME;
keep REF_STAT ACCNT_NUM REPORT_DATE REPORT_TIME;
input REF_STAT ACCNT_NUM_tmp REPORT_DATE_tmp REPORT_TIME_tmp;
if ACCNT_NUM_tmp ne '' then ACCNT_NUM = ACCNT_NUM_tmp;
if REPORT_DATE_tmp ne '' then REPORT_DATE = REPORT_DATE_tmp;
if REPORT_TIME_tmp ne '' then REPORT_TIME = REPORT_TIME_tmp;
if REF_STAT ne '' then output;
datalines;
. . 05/13/17 08:57:55
. 0000010xxxxxxxx . .
ACT . . .
ACT . . .
ACT . . .
;
run;
Thanks FredrickE.
I can locate the header data and populate initial variables in the dataset when they occur.
REF_STAT ACCNT_NUM REPORT_DATE REPORT_TIME
05/13/17 08:57:55
0000010xxxxxxxx
I can use the following to populate all following observations with the data in new varables:
DATA WORK.QUERY_FOR__RENO_RAW;
SET WORK.QUERY_FOR__RENO_RAW1;
RETAIN ACCNT_NUM2;
IF ACCNT_NUM>. THEN ACCNT_NUM2=ACCNT_NUM;
RUN;
This also works for date and time when the IF statement is changed to variable >' '.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.