Help using Base SAS procedures

Reg :Continous obs

Reply
Regular Contributor
Posts: 229

Reg :Continous obs

Hi i ma having a variable id if it is continously geeting 1 ,2,3 then flag should create as 1 based on accno

dataset
data n;
input aaccno id;
cards;
23 0
23 1
23 2
23 3
23 0
24 1
24 4
24 2
24 3
26 1
26 2
26 3
run;

output:

data n;
input id flag;
cards;
0 0
1 1
2 1
3 1
0 0
1 0
4 0
2 0
3 0
1 1
2 1
3 1
run;
N/A
Posts: 0

Re: Reg :Continous obs

hello,

you can try merging data with itself without by statement:

[pre]
data n;
input aaccno id;
cards;
23 0
23 1
23 2
23 3
23 0
24 1
24 4
24 2
24 3
26 1
26 2
26 3
run;

data x;
merge n n(firstobs=2 rename=(id=id2 aaccno=aaccno2)) n(firstobs=3 rename=(id=id3 aaccno=aaccno3));

retain flag;

if aaccno=aaccno2=aaccno3 and id=1 and id2=2 and id3=3 then flag=1;
if id not in (1,2,3) then flag=0;

keep aaccno id flag;

run;
[/pre]

Marius
N/A
Posts: 0

Re: Reg :Continous obs

Posted in reply to deleted_user
Hello,

I've looked upon my code and there where some situations which it does not cover.
So I have added another if condition:

[pre]
data x;
merge n n(firstobs=2 rename=(id=id2 aaccno=aaccno2)) n(firstobs=3 rename=(id=id3 aaccno=aaccno3));

retain flag ;

if aaccno ne lag(aaccno) or id-lag(id) ne 1 then flag=0;*reset flag when first aaccno or id not continous;
if aaccno=aaccno2=aaccno3 and id=1 and id2=2 and id3=3 then flag=1;
if id not in (1,2,3) then flag=0;

keep aaccno id flag;

run;
[/pre]

Marius
Super User
Posts: 10,023

Re: Reg :Continous obs

OK.
I think your task is tough,so force me to use hash table as a combination of arrarys .



[pre]

data n;
input aaccno id;
count+1;
cards;
23 0
23 1
23 2
23 3
23 0
24 1
24 4
24 2
24 3
26 1
26 2
26 3
;
run;

data want(keep=aaccno id flag);
declare hash hh(hashexp: 10);
hh.definekey('count','aaccno','id');
hh.definedone();

do until(last);
set n end=last;
if id=3 and lag(id)=2 and lag2(id)=1 and
aaccno=lag(aaccno) and aaccno=lag2(aaccno) then do;
hh.add();
_count=count-1;
set n point=_count;
hh.add();
_count=count-1;
set n point=_count;
hh.add();
end;
end;

do until(_last);
set n end=_last;
rc=hh.check();
if rc eq 0 then flag=1;
else flag=0;
output;
end;
stop;
run;


[/pre]


Ksharp
Regular Contributor
Posts: 229

Re: Reg :Continous obs

THQS
Super User
Posts: 10,023

Re: Reg :Continous obs

Hi.
Actually, My code can be refined as :


[pre]
data n;
input aaccno id;
count+1;
cards;
23 0
23 1
23 2
23 3
23 0
24 1
24 4
24 2
24 3
26 1
26 2
26 3
;
run;

data want(keep=aaccno id flag);
declare hash hh(hashexp: 10);
hh.definekey('count');
hh.definedone();

do until(last);
set n end=last;
if id=3 and lag(id)=2 and lag2(id)=1 and
aaccno=lag(aaccno) and aaccno=lag2(aaccno) then do;
hh.add();
count=count-1;
hh.add();
count=count-1;
hh.add();
end;
end;

do until(_last);
set n end=_last;
rc=hh.check();
if rc eq 0 then flag=1;
else flag=0;
output;
end;
stop;
run;
[/pre]



Ksharp
Super User
Posts: 10,023

Re: Reg :Continous obs

Maybe you want pure data step code which is more readable:


[pre]
data n;
input aaccno id;
count+1;
cards;
23 0
23 1
23 2
23 3
23 0
24 1
24 4
24 2
24 3
26 1
26 2
26 3
;
run;
data _n;
set n;
if id=3 and lag(id)=2 and lag2(id)=1 and
aaccno=lag(aaccno) and aaccno=lag2(aaccno) then output;
run;
data _n;
set _n;
output;
count=count-1;output;
count=count-1;output;
keep count;
run;
proc sort data=_n;
by count;
run;
data want;
merge n _n(in=in__n);
by count;
if in__n then flag=1;
else flag=0;
keep id flag;
run;
[/pre]



Ksharp
Ask a Question
Discussion stats
  • 6 replies
  • 167 views
  • 0 likes
  • 3 in conversation