BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Suhaa
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

3 REPLIES 3
Suhaa
Calcite | Level 5

Please suggest if you cannot answer exactly..

Haikuo
Onyx | Level 15

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

Suhaa
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 754 views
  • 0 likes
  • 2 in conversation