BookmarkSubscribeRSS Feed
Alankar
Fluorite | Level 6
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
11 REPLIES 11
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Fluorite | Level 6
Thanks Scott, Got it!!
Ksharp
Super User
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
Super User
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
Opal | Level 21
Hi Ksharp
But with a "hashexp:16" you're actually expecting a very large table 😉
Cheers
Patrick
Ksharp
Super User
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__
Jade | Level 19
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
Quartz | Level 8
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
Super User
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
SAS Super FREQ
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
Rhodochrosite | Level 12
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

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
  • 11 replies
  • 1865 views
  • 0 likes
  • 8 in conversation