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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
Peter_C
Rhodochrosite | Level 12

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.

stat_sas
Ammonite | Level 13

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;

rajeshm
Quartz | Level 8

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.

data_null__
Jade | Level 19

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;
stat_sas
Ammonite | Level 13

proc sql;
select sum(purge='p') into :p_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;

Astounding
PROC Star

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.

rajeshm
Quartz | Level 8

thanks a lot. simply superb.

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

rajeshm
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 1641 views
  • 0 likes
  • 6 in conversation