topic Re: to read a file and re align the fields based on some condition.. in SAS Procedures

to read a file and re align the fields based on some condition..

Suhaa — Wed, 16 Oct 2013 09:50:44 GMT

Hi...
I need your help in achiving this below output with sas programming.
I am not sure about the complexity may be simple.. But i am a beginner so please help..

Input file definition:

SEQ $2.
TRN $2.
KEY $5.
DAT $5.

Input:
0101AAAAA11111
0202AAAAA22222
0309AAAAAZZZZZ
0409BBBBBYYYYY
0501CCCCC33333
0603CCCCC44444
0709CCCCCXXXXX
0803DDDDD55555
0909DDDDDWWWWW

Expected output:
0101AAAAA11111ZZZZZ
0202AAAAA22222ZZZZZ
0409BBBBB YYYYY
0501CCCCC33333XXXXX
0603CCCCC44444XXXXX
0803DDDDD55555WWWWW

output file def :

SEQ $2.
TRN $2.
KEY $5.
DAT $5.
dat09 $5.

To read the entire file sequentially and perform this logic and write write full record for < 09 transactions and the dat field from 09 should append at the end of all prior<09 transactions.

Let me know if you need any more explaination.

Re: to read a file and re align the fields based on some condition..

Suhaa — Sat, 19 Oct 2013 15:34:36 GMT

Please suggest if you cannot answer exactly..

Re: to read a file and re align the fields based on some condition..

Haikuo — Sat, 19 Oct 2013 18:57:29 GMT

I did some guessing on your rules, as I believe that the ambiguity of your rules must be the reason that you haven't got any answers yet. The following approaches give what you asked as output, but if that does not meet your requirement, please spell out your rules in details.

data have;

input

SEQ $2.

TRN $2.

KEY $5.

DAT $5.

;

cards;

0101AAAAA11111

0202AAAAA22222

0309AAAAAZZZZZ

0409BBBBBYYYYY

0501CCCCC33333

0603CCCCC44444

0709CCCCCXXXXX

0803DDDDD55555

0909DDDDDWWWWW

;

/*Proc SQL*/

proc sql;

create table want_sql as

select a.seq,a.trn,a.key, case when a.trn='09' then '' else a.dat end as dat, b.dat as dat09

from have a

left join have b

on a.key=b.key

and b.trn='09'

group by a.key

having count(a.seq)=1 or count(a.seq) >1 and a.trn ne '09'

order by seq

;

quit;

/*Date Step*/

Data want_ds;

do until (last.key);

set have;

by key notsorted;

if trn='09' then dat09=dat;

end;

do until (last.key);

set have;

by key notsorted;

if last.key then do;

if not first.key then delete;

else call missing (dat);

end;

output;

end;

run;

Haikuo

Re: to read a file and re align the fields based on some condition..

Suhaa — Mon, 21 Oct 2013 09:06:11 GMT

Thanks a lot for helping me..

I tried both of your approach and i got then expected results from second method.

could you please explaing the second part of your code, Only after understanding i can apply to my original data file.

do until (last.key);

set have;

by key notsorted;

if last.key then do;

if not first.key then delete;

else call missing (dat);

end;

output;

end;