DATA Step, Macro, Functions and more

Retain Last.var value in new variable

Reply
Contributor
Posts: 26

Retain Last.var value in new variable

THE INITIAL DATA IS LIKE THIS.
A 1
A 2
B 1
B 2
B 3
B 4
C 1
D 1
D 2
D 3


I WANT TO HAVE THE LAST.NUM VALUSE TO BE MY NEW COLUMN FOR EACH CUSTOMERS.
A 1 2
A 2 2
B 1 4
B 2 4
B 3 4
B 4 4
C 1 1
D 1 3
D 2 3
D 3 3


I KNOW THAT I CAN CREATE SEPERATE DATASET WITH LAST.NUM AND MERGE AGAIN WITH THIS DAT, I'M JUST CURIOUS TO KNOW IF THERE IS ANY OTHER WAY WE CAN DO IN SINGLE DATASTEP. BECAUSE I'M CREATING "NUM" ALSO IN THE SAME DATA STEP.

THANKS IN ADVANCE,
ALANKAR
CITI
Super Contributor
Super Contributor
Posts: 3,174

Re: Retain Last.var value in new variable

If you are assigning a NUM variable value, then you are not able to use a BY statement. Also, anyway, there is no look-forward called function with the SAS DATA step approach.

Given the scenario you suggest and limitations, I would say the answer is no. Why the "same DATA step" requirement anyway? Suggest you exploit the SAS facilities without such strange limitations and control/processing specifications.

Scott Barry
SBBWorks, Inc. Message was edited by: sbb
Contributor
Posts: 26

Re: Retain Last.var value in new variable

Thanks Scott, Got it!!
Super User
Posts: 10,041

Re: Retain Last.var value in new variable

Emmmm.Let me try it.
I think SAS has this power.But this method is not suited for large table which will very slowly.
[pre]






data temp;
input id $ _num;
cards;
A 1
A 2
B 1
B 2
B 3
B 4
C 1
D 1
D 2
D 3
;
run;
data want(drop=_num rc count);
set temp end=last;
by id notsorted;
count+1;
num=_num*2 +1;
if _n_ eq 1 then do;
declare hash _hh(hashexp:16);
_hh.definekey('id','count');
_hh.definedata('num');
_hh.definedone();
declare hash hh(hashexp:10);
hh.definekey('id');
hh.definedata('last_num');
hh.definedone();
end;

rc=_hh.add();
if last.id then do;last_num=num; rc=hh.add(); end;
if last then do;
count=0;
do until(_last);
set temp end=_last;
call missing(last_num);
count+1;
_hh.find();
hh.find();
output;
end;
end;
run;
[/pre]



Ksharp
Super User
Posts: 10,041

Re: Retain Last.var value in new variable

Emmmm.Let me try it.
I think SAS has this power.But this method is not suited for large table which will very slowly.
[pre]






data temp;
input id $ _num;
cards;
A 1
A 2
B 1
B 2
B 3
B 4
C 1
D 1
D 2
D 3
;
run;
data want(drop=_num rc count);
set temp end=last;
by id notsorted;
count+1;
num=_num*2 +1;
if _n_ eq 1 then do;
declare hash _hh(hashexp:16);
_hh.definekey('id','count');
_hh.definedata('num');
_hh.definedone();
declare hash hh(hashexp:10);
hh.definekey('id');
hh.definedata('last_num');
hh.definedone();
end;

rc=_hh.add();
if last.id then do;last_num=num; rc=hh.add(); end;
if last then do;
count=0;
do until(_last);
set temp end=_last;
call missing(last_num);
count+1;
_hh.find();
hh.find();
output;
end;
end;
run;
[/pre]



Ksharp
Respected Advisor
Posts: 4,173

Re: Retain Last.var value in new variable

Hi Ksharp
But with a "hashexp:16" you're actually expecting a very large table ;-)
Cheers
Patrick
Super User
Posts: 10,041

Re: Retain Last.var value in new variable

Hi.Patrick.
Yes.But as we know HashTable has some limitation with memory.
And My method is to read original dataset twice.So it will yield low efficiency for large table.
hashexp:16 just want to let it faster.


Cheers!
Ksharp
Respected Advisor
Posts: 3,799

Re: Retain Last.var value in new variable

Basically same as merging with summary data but in one step.

[pre]
data have;
input a $ b @@;
cards;
A 1 A 2
B 1 B 2 B 3 B 4
C 1
D 1 D 2 D 3
;;;;
run;
data need;
do _n_ = 1 by 1 until(last.a);
set have(rename=(b=c));
by a;
end;
do _n_ = 1 to _n_;
set have;
output;
end;
run;
proc print;
run;
[/pre]
Regular Contributor
Posts: 184

Re: Retain Last.var value in new variable

Posted in reply to data_null__
Or, the self-interleave technique:

[pre]
data have ;
input a $ @@ ;
cards ;
A A
B B B B
C
D D D
;

data need ;
set have(in=pass1)
have(in=pass2) ;
by a ;
if first.a then call missing(b,c) ;
if pass1 then c + 1 ;
if pass2 then do ; b + 1 ; output ; end ;
run ;
[/pre]

> Basically same as merging with summary data but in
> one step.
>
> [pre]
> data have;
> input a $ b @@;
> cards;
> A 1 A 2
> B 1 B 2 B 3 B 4
> C 1
> D 1 D 2 D 3
> ;;;;
> run;
> a need;
> do _n_ = 1 by 1 until(last.a);
> set have(rename=(b=c));
> by a;
> end;
> do _n_ = 1 to _n_;
> set have;
> output;
> end;
> ;
> proc print;
> run;
> re]
Super User
Posts: 10,041

Re: Retain Last.var value in new variable

Hi.
But the column OP want is the calculated column in the same data step.
That is the problem where it is.

Ksharp
SAS Super FREQ
Posts: 8,868

Re: Retain Last.var value in new variable

Hi:
If you have the resources to do 2 sorts, then you could use BY group processing and RETAIN. This program is more simple than using a HASH table, but does require that you sort the _NUM variable in descending order before the DATA step and then sort again after the DATA step.

cynthia

This output:
[pre]
Obs id _num holdnum

1 A 1 2
2 A 2 2
3 B 1 4
4 B 2 4
5 B 3 4
6 B 4 4
7 C 1 1
8 D 1 3
9 D 2 3
10 D 3 3

[/pre]

from this program:
[pre]
data temp;
input id $ _num;
return;
datalines;
A 1
A 2
B 1
B 2
B 3
B 4
C 1
D 1
D 2
D 3
;
run;

proc sort data=temp out=temp;
by id descending _num;
run;

data new;
set temp;
by id descending _num;
retain holdnum;
if first.id then holdnum = _num;

putlog id= _num= holdnum=;
output;
run;

proc sort data=new out=new;
by id _num;
run;

proc print data=new;
title 'After sorting new data';
run;
[/pre]
Valued Guide
Posts: 2,177

Re: Retain Last.var value in new variable

first.var and last.var processing applies to var coming through by-processing. So you cannot use this type of handling on variables you create in the step.
If the column already exists in a table, you cannot just add another column, without writing out the data again, unless:
you use data step views.
* set creating NUM ;
data something /view = something ;
keep ID num other stuff ;
* neccessary processing ;
run ;
data lastnum /view=lastnum ;
set something ;
by id num ;
if last.id ;
keep id num ;
run ;
data finally ;
merge something lastnum( rename= num=finalNum );
by ID ;
run ;

of course, the second VIEW depends on having the first VIEW create its output in by-group order = ID NUM . When this is not possible, it could be replaced with an SQL view - but that is just sorting under the covers.
PROC SORT when you need to.

peterC
Ask a Question
Discussion stats
  • 11 replies
  • 285 views
  • 0 likes
  • 8 in conversation