topic Re: Retain Last.var value in new variable in SAS Programming

Retain Last.var value in new variable

Alankar — Fri, 03 Jun 2011 02:36:45 GMT

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

Re: Retain Last.var value in new variable

sbb — Fri, 03 Jun 2011 03:51:37 GMT

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

Re: Retain Last.var value in new variable

Alankar — Fri, 03 Jun 2011 05:35:01 GMT

Thanks Scott, Got it!!

Re: Retain Last.var value in new variable

Ksharp — Fri, 03 Jun 2011 06:05:05 GMT

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

Re: Retain Last.var value in new variable

Ksharp — Fri, 03 Jun 2011 06:09:49 GMT

Re: Retain Last.var value in new variable

Patrick — Fri, 03 Jun 2011 11:49:47 GMT

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

Re: Retain Last.var value in new variable

data_null__ — Fri, 03 Jun 2011 13:04:13 GMT

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]

Re: Retain Last.var value in new variable

Cynthia_sas — Fri, 03 Jun 2011 14:48:32 GMT

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]

Re: Retain Last.var value in new variable

Peter_C — Fri, 03 Jun 2011 15:32:37 GMT

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

Re: Retain Last.var value in new variable

Howles — Sat, 04 Jun 2011 22:00:06 GMT

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]

Re: Retain Last.var value in new variable

Ksharp — Tue, 07 Jun 2011 02:30:43 GMT

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

Re: Retain Last.var value in new variable

Ksharp — Tue, 07 Jun 2011 02:47:54 GMT

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

Ksharp