Retain Last.var value in new variable

[Alankar]

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

[sbb]

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

[Alankar]

Thanks Scott, Got it!!

[Ksharp]

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

[Ksharp]

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

[Patrick]

Hi Ksharp
But with a "hashexp:16" you're actually expecting a very large table 😉
Cheers
Patrick

[Ksharp]

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

[data_null__]

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]

[Howles]

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]

[Ksharp]

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

Ksharp

[Cynthia_sas]

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]

[Peter_C]

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