BookmarkSubscribeRSS Feed
R_Win
Calcite | Level 5
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;
6 REPLIES 6
deleted_user
Not applicable
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
deleted_user
Not applicable
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
Ksharp
Super User
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
R_Win
Calcite | Level 5
THQS
Ksharp
Super User
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
Ksharp
Super User
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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 766 views
  • 0 likes
  • 3 in conversation