Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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.


Accepted Solutions
Solution
‎10-19-2013 02:57 PM
Respected Advisor
Posts: 3,124

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

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


All Replies
Occasional Contributor
Posts: 5

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

Please suggest if you cannot answer exactly..

Solution
‎10-19-2013 02:57 PM
Respected Advisor
Posts: 3,124

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

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

Occasional Contributor
Posts: 5

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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