topic first.last and last.id in SAS Programming

first.last and last.id

rajeshm — Sun, 24 Aug 2014 19:09:55 GMT

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;

Re: first.last and last.id

Peter_C — Sun, 24 Aug 2014 19:42:37 GMT

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.

Re: first.last and last.id

stat_sas — Sun, 24 Aug 2014 21:31:57 GMT

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;

Re: first.last and last.id

rajeshm — Mon, 25 Aug 2014 07:14:15 GMT

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.

Re: first.last and last.id

data_null__ — Mon, 25 Aug 2014 16:05:23 GMT

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;

Re: first.last and last.id

Astounding — Mon, 25 Aug 2014 16:41:39 GMT

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.

Re: first.last and last.id

Haikuo — Mon, 25 Aug 2014 19:06:22 GMT

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

Re: first.last and last.id

stat_sas — Mon, 25 Aug 2014 19:15:51 GMT

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;

Re: first.last and last.id

rajeshm — Tue, 26 Aug 2014 04:46:34 GMT

thanks a lot. simply superb.

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

Re: first.last and last.id

rajeshm — Fri, 29 Aug 2014 09:59:10 GMT

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