DATA Step, Macro, Functions and more

first.last and last.id

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

first.last and last.id

I need the output of    purge='n'  and  record having highest date with purge='p' .

data purged;

input acc purge$ date ;

datalines;

111  p 234

234  n 1333

1111 p 2345

2234 n 1333

1121 p 2334

2334 n 3233

;

run;

proc sort data=purged;

by   purge date ;

run;

data purgedorder;

set purged;

by  purge  date;* the following code is working fine.

*by date purge; * if my by statement like this,Need logic for this

if purge='n' then output;

else if(last.purge=1 and last.date=1) then output;

run;

proc print;

run;


Accepted Solutions
Solution
‎08-25-2014 12:41 PM
Super User
Posts: 5,516

Re: first.last and last.id

A little complex, but not terribly lengthy:

data want;

   set have;

   by date purge end=done;

   if purge='n' then output;

   else recno=_n_;

   retain recno;

   if done then do;

      set have point=recno;

      output;

      stop;

   end;

   drop recno;

run;

This assumes you need just one purged record.  If you need more than one (such as one per date), it can still be done.  But the question looks like you are asking for just one.

View solution in original post


All Replies
Valued Guide
Posts: 2,177

Re: first.last and last.id

Perhaps easier with sql 

PROC SQL ;

  CREATE TABLE  selection AS

  SELECT a.*

       FROM purged  A

    WHERE a.purge

       OR a.date = MAX( a.date )

     ;

QUIT ;

If course there is always a "but".

What should happen when 2 ACC have the max date?

The sql code above would select both.

Trusted Advisor
Posts: 1,228

Re: first.last and last.id

proc sql;

create table want as

select * from purged

where purge='n'

union all

select * from purged

where purge='p'

having date=max(date);

quit;

Contributor
Posts: 44

Re: first.last and last.id

hi,

thanks for the help. please let me know  the logic in datastep only.

by  purge  date;* the following code is working fine.

by date purge; * if my by statement like this,Need logic for this

reason: mistakenly I sorted in the second way and struggled a lot to get the answer.I want to know whether can we get the result or not ,if we sorted in second way.

Respected Advisor
Posts: 3,799

Re: first.last and last.id

This?

data purged;
   input acc purge$ date;
   datalines;
111  p 234
234  n 1333
1111 p 2345
2234 n 1333
1121 p 2334
2334 n 3233
;
run;
proc sort data=purged;
   by purge descending date;
   run;
data purgedorder;
   set purged;
   by  purge;
   if purge eq 'n' then output;
  
else if first.purge then output;
  
run;
proc print;
  
run;
Trusted Advisor
Posts: 1,228

Re: first.last and last.id

proc sql;
select sum(purge='p') into Smiley Tongue_count from purged;
quit;

proc sort data=purged;
by date purge;
run;

data want(drop=cnt);
set purged;
by date purge;
if purge='p' then cnt+1;
if purge='n' or (purge='p' and cnt=&p_count);
run;

Solution
‎08-25-2014 12:41 PM
Super User
Posts: 5,516

Re: first.last and last.id

A little complex, but not terribly lengthy:

data want;

   set have;

   by date purge end=done;

   if purge='n' then output;

   else recno=_n_;

   retain recno;

   if done then do;

      set have point=recno;

      output;

      stop;

   end;

   drop recno;

run;

This assumes you need just one purged record.  If you need more than one (such as one per date), it can still be done.  But the question looks like you are asking for just one.

Contributor
Posts: 44

Re: first.last and last.id

Posted in reply to Astounding

thanks a lot. simply superb.

I mistakenly sorted in this order and could not get the answer. got the logic now.

Contributor
Posts: 44

Re: first.last and last.id

Posted in reply to Astounding

hi,

could u please answer this one. 

question:  sas has to create a value/invalue statements for the given dataset (cntlin option for dataset2format convertion).based on what, it is creating value/invalue statements or sas creating both value/invalue statements for the given dataset?

thanks in advance

Respected Advisor
Posts: 3,156

Re: first.last and last.id

Hash is also considered as Data Step implementation:

data purged;

     input acc purge$ date;

     datalines;

111  p 234

234  n 1333

1111 p 2345

2234 n 1333

1121 p 2334

2334 n 3233

;

run;

data want;

     if _n_=1 then

           do;

                declare hash h(ordered: 'a');

                h.definekey('date');

                h.definedata('purge','acc', 'date');

                h.definedone();

                declare hiter hi('h');

           end;

     set purged end=last;

     if purge='n' then

           output;

     else if purge='p' then

           rc=h.replace();

     if last then

           do;

                rc=hi.last();

                output;

           end;

     drop rc;

run;

Haikuo

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 456 views
  • 0 likes
  • 6 in conversation