Well, I made modifications to Reeza/ballardw/Shmuel's code below and got it to run; however, I am not getting the right results. The code parses the data correctly and splits it up, but the problem seems to be when putting the data back together to get one record per w_num (some fields are accurate for each record, while other fields belong to some other record). I'm not sure how or why that is happening. Also, how can I make the code below more efficient? I am a fairly new SAS user, so I am sure the modifications I made weren't the best way to go... I pasted the code below and attached it as well. data new;
infile 'my location';
length c_num $4 w_num $7 r_type $1 spa $12
account_num $16 csr $3 TEAM_ID $3 REASSIGN_REASON $2
W_TYPE $8 other $14 receipt_date $8 entry_date $8
other2 $29 COMPLIANCE_DATE $8 EXCEPTION_DATE $8
REVIEW_DATE $8 RESOLVE_DATE $8 other3 $56 addr_flag $1
name $104 addr $104 city_state_zip $30 other4 $282
phone $38 other5 $19 action_type $8 queue_ID $8
other6 $73 other7 $73 other8 $83 other9 $73
other10 $101 other11 $7 other12 $44;
retain c_num w_num r_type spa account_num
csr TEAM_ID REASSIGN_REASON W_TYPE other
receipt_date entry_date other2 COMPLIANCE_DATE
EXCEPTION_DATE REVIEW_DATE RESOLVE_DATE other3
addr_flag name addr city_state_zip other4 phone
other5 action_type queue_ID other6 other7 other8
other9 other10 other11 other12;
informat r_type $1.;
input r_type 12 @@;
select (r_type);
when ('0') input c_num 1-4 w_num 5-11
spa 13-24 account_num 25-40 csr 41-43
TEAM_ID 44-46 REASSIGN_REASON 47-48
W_TYPE 49-56 other 57-70
receipt_date 71-78 entry_date 79-86
other2 87-115 COMPLIANCE_DATE 116-123
EXCEPTION_DATE 124-131
REVIEW_DATE 132-139
RESOLVE_DATE 140-147 other3 148-203;
when ('1') input c_num 1-4 w_num 5-11
addr_flag 13 name 14-117 addr 118-221
city_state_zip 222-251 other4 252-533
phone 534-571 other5 572-590;
when ('2') input c_num 1-4 w_num 5-11
action_type 13-20 queue_ID 21-28
other6 29-101;
when ('3') input c_num 1-4 w_num 5-11
other7 13-85;
when ('4') input c_num 1-4 w_num 5-11
other8 13-95;
when ('5') input c_num 1-4 w_num 5-11
other9 13-85;
when ('6') input c_num 1-4 w_num 5-11
other10 13-113;
when ('9') input c_num 1-4 other11 5-11
other12 13-56;
otherwise Put "Other value" +1 r_type = $1.
+1 "on line" +1 _n_;
end;
run;
proc sort data=new;
by w_num r_type;
run;
data want;
set new end=eof; /* eof is assigned variable indicator for End-Of-File */
by w_num r_type;
length c_num $4 w_num $7 r_type $1 spa $12
account_num $16 csr $3 TEAM_ID $3 REASSIGN_REASON $2
W_TYPE $8 other $14 receipt_date $8 entry_date $8
other2 $29 COMPLIANCE_DATE $8 EXCEPTION_DATE $8
REVIEW_DATE $8 RESOLVE_DATE $8 other3 $56 addr_flag $1
name $104 addr $104 city_state_zip $30 other4 $282
phone $38 other5 $19 action_type $8 queue_ID $8
other6 $73 other7 $73 other8 $83 other9 $73
other10 $101 other11 $7 other12 $44;
retain c_num w_num r_type spa account_num
csr TEAM_ID REASSIGN_REASON W_TYPE other
receipt_date entry_date other2 COMPLIANCE_DATE
EXCEPTION_DATE REVIEW_DATE RESOLVE_DATE other3
addr_flag name addr city_state_zip other4 phone
other5 action_type queue_ID other6 other7 other8
other9 other10 other11 other12;
array rest $ spa account_num
csr TEAM_ID REASSIGN_REASON W_TYPE other
receipt_date entry_date other2 COMPLIANCE_DATE
EXCEPTION_DATE REVIEW_DATE RESOLVE_DATE other3
addr_flag name addr city_state_zip other4 phone
other5 action_type queue_ID other6 other7 other8
other9 other10 other11 other12;
array typs {10} t0-t9; /* checking are all r_types exist */
if first.w_num then do;
if _N_ > 1 then output; /* new record and not the first in input */
do i=1 to dim(rest); call missing(rest(i)); end; /* clean for new record */
do i=1 to 9; typs(i)=0; end; /* 0=absent, 1=exist */
end; else
select (r_type);
when (0) link in_typ0;
when (1) link in_typ1;
when (2) link in_typ2;
when (3) link in_typ3;
when (4) link in_typ4;
when (5) link in_typ5;
when (6) link in_typ6;
when (9) link in_typ9;
otherwise Put "Other value " r_type= ;
end;
if eof then output; /* output last record in input */
drop i row_rest t0-t9 r_type;
return;
/*------------------------*/
in_typ0:
/* parse row_rest when r_type = 0 */
if t0 = 1 then do;
put '**Duplicate r_type=0 for ' c_num= w_num= ;
return;
end;
t0=1;
spa = substr(row_rest,1,12);
receipt_date = substr(row_rest,59,8);
return;
/*------------------------*/
in_typ1:
/* parse row_rest when r_type = 1 */
if t1 = 1 then do;
put '**Duplicate r_type=1 for ' c_num= w_num= ;
return;
end;
t1 = 1;
name = row_rest;
return;
/*------------------------*/
in_typ2:
/* parse row_rest when r_type = 2 */
if t2 = 1 then do;
put '**Duplicate r_type=2 for ' c_num= w_num= ;
return;
end;
t2 = 1;
action_type = substr(row_rest,1,8);
return;
/*------------------------*/
in_typ3:
/* parse row_rest when r_type = 3 */
if t3 = 1 then do;
put '**Duplicate r_type=3 for ' c_num= w_num= ;
return;
end;
t3 = 1;
var_name = substr(row_rest,1,20);
return;
/*------------------------*/
in_typ4:
/* parse row_rest when r_type = 4 */
if t4 = 1 then do;
put '**Duplicate r_type=4 for ' c_num= w_num= ;
return;
end;
t4 =1;
return;
/*------------------------*/
in_typ5:
/* parse row_rest when r_type = 5 */
if t5 = 1 then do;
put '**Duplicate r_type=5 for ' c_num= w_num= ;
return;
end;
t5 = 1;
return;
/*------------------------*/
in_typ6:
/* parse row_rest when r_type = 6 */
if t6 = 1 then do;
put '**Duplicate r_type=6 for ' c_num= w_num= ;
return;
end;
t6 = 1;
return;
/*------------------------*/
in_typ9:
/* parse row_rest when r_type = 9 */
if t9 = 1 then do;
put '**Duplicate r_type=9 for ' c_num= w_num= ;
return;
end;
t9 = 1;
return;
/*------------------------*/
run;
... View more
