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.
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
Please suggest if you cannot answer exactly..
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.