cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
chinna0369
Pyrite | Level 9 chinna0369
Pyrite | Level 9
Go to Solution

flag first record if there are consecutive same values by group

Posted 03-19-2024 01:05 PM (978 views)

Hi,

 

I would like flag first record if there are 5 or more consecutive records by group after sorting. I have provided the data below and the code which I am trying but it is not working. Can you please correct me where I am doing wrong? 

We need to sort by subjid parcat1 visit paramn. if consecutive values should be there for consecutive paramn values. If there is any consecutive paramn is missing in between 5 consecutive aval then it shouldn't be flagged. Thanks in advance! 

 

data have;
input parcat1 $1-6 SUBJID $7-15 aval 16-17	paramn 18-21 visit $22-26;
datalines;
DAILY	10005501	0	301	V1
DAILY	10005501	1	302	V1
DAILY	10005501	0	303	V1
DAILY	10005501	1	304	V1
DAILY	10005501	0	305	V1
DAILY	10005501	2	306	V1
DAILY	10005501	2	307	V1
DAILY	10005501	2	308	V1
DAILY	10005501	0	309	V1
DAILY	10005501	2	310	V1
DAILY	10005501	0	311	V1
DAILY	10005501	0	312	V1
DAILY	10005501	1	313	V1
DAILY	10005501	0	314	V1
DAILY	10005501	1	315	V1
DAILY	10005501	2	316	V1
DAILY	10005501	1	317	V1
DAILY	10005501	2	318	V1
DAILY	10005501	0	319	V1
DAILY	10005501	0	320	V1
DAILY	10005501	0	321	V1
DAILY	10005501	0	322	V1
DAILY	10005501	2	323	V1
DAILY	10005501	0	324	V1
DAILY	10005501	0	346	V1
DAILY	10005501	0	347	V1
DAILY	10005501	0	348	V1
DAILY	10005501	0	349	V1
DAILY	10005501	0	350	V1
SOCIAL	10007501	1	301	V11
SOCIAL	10007501	2	303	V11
SOCIAL	10007501	2	304	V11
SOCIAL	10007501	0	305	V11
SOCIAL	10007501	0	306	V11
SOCIAL	10007501	2	307	V11
SOCIAL	10007501	0	308	V11
SOCIAL	10007501	0	309	V11
SOCIAL	10007501	2	310	V11
SOCIAL	10007501	0	311	V11
SOCIAL	10007501	0	312	V11
SOCIAL	10007501	2	313	V11
SOCIAL	10007501	0	314	V11
SOCIAL	10007501	0	315	V11
SOCIAL	10007501	0	316	V11
SOCIAL	10007501	0	317	V11
SOCIAL	10007501	0	319	V11
SOCIAL	10007501	0	320	V11
SOCIAL	10007501	0	321	V11
SOCIAL	10007501	0	322	V11
SOCIAL	10007501	0	323	V11
SOCIAL	10007501	0	325	V11
SOCIAL	10007501	0	326	V11
;
run;
data want;
input parcat1 $1-6 SUBJID $7-15 aval 16-17 paramn 18-21 visit $22-26 flag $28;
datalines;
DAILY 10005501 0 301 V1
DAILY 10005501 1 302 V1
DAILY 10005501 0 303 V1
DAILY 10005501 1 304 V1
DAILY 10005501 0 305 V1
DAILY 10005501 2 306 V1
DAILY 10005501 2 307 V1
DAILY 10005501 2 308 V1
DAILY 10005501 0 309 V1
DAILY 10005501 2 310 V1
DAILY 10005501 0 311 V1
DAILY 10005501 0 312 V1
DAILY 10005501 1 313 V1
DAILY 10005501 0 314 V1
DAILY 10005501 1 315 V1
DAILY 10005501 2 316 V1
DAILY 10005501 1 317 V1
DAILY 10005501 2 318 V1
DAILY 10005501 0 319 V1
DAILY 10005501 0 320 V1
DAILY 10005501 0 321 V1
DAILY 10005501 0 322 V1
DAILY 10005501 2 323 V1
DAILY 10005501 0 324 V1
DAILY 10005501 0 346 V1 Y
DAILY 10005501 0 347 V1
DAILY 10005501 0 348 V1
DAILY 10005501 0 349 V1
DAILY 10005501 0 350 V1
SOCIAL 10007501 1 301 V11
SOCIAL 10007501 2 303 V11
SOCIAL 10007501 2 304 V11
SOCIAL 10007501 0 305 V11
SOCIAL 10007501 0 306 V11
SOCIAL 10007501 2 307 V11
SOCIAL 10007501 0 308 V11
SOCIAL 10007501 0 309 V11
SOCIAL 10007501 2 310 V11
SOCIAL 10007501 0 311 V11
SOCIAL 10007501 0 312 V11
SOCIAL 10007501 2 313 V11
SOCIAL 10007501 0 314 V11
SOCIAL 10007501 0 315 V11
SOCIAL 10007501 0 316 V11
SOCIAL 10007501 0 317 V11
SOCIAL 10007501 0 319 V11 Y
SOCIAL 10007501 0 320 V11
SOCIAL 10007501 0 321 V11
SOCIAL 10007501 0 322 V11
SOCIAL 10007501 0 323 V11
SOCIAL 10007501 0 325 V11
SOCIAL 10007501 0 326 V11
;
run;
proc sort data=have; by subjid parcat1 visit paramn aval; run;
data want;
do m=1 by 1 until(last.aval);
set have;
by visit parcat1 subjid aval notsorted;
end; 
do until(last.aval);
set have;
by visit parcat1 subjid aval notsorted;
if first.aval and m>4 and aval=0 then flag='Y';
output;
end;
run;
proc sort; by subjid parcat1 visit paramn aval; run;

Note: I posted this question last week with different logic now I have different logic.

 

Thanks,

An

0 Likes
Reply
1 ACCEPTED SOLUTION

Accepted Solutions
Mazi
Pyrite | Level 9 Mazi
Pyrite | Level 9

Re: flag first record if there are consecutive same values by group

Posted 03-19-2024 03:29 PM (837 views)  |   In reply to chinna0369 
proc sort data=have; by subjid parcat1 visit paramn aval; run;
data want2;
	do m=1 by 1 until(last.aval);
		set have;
		by subjid parcat1 visit aval notsorted;
		if first.aval then do;
			x=paramn; 
			n=1; 	
			count=1; 
		end;
		else if paramn = sum(x,n) then do; 
			n+1; 
			count+1; 
		end;
		else do;
			count=1;
			x=paramn;
			n=1;
		end;
		if count>4 then do;
			aa=x;
			bb=1;
		end;
	end; 
	do until(last.aval);
		set have;
		by subjid parcat1 visit aval paramn notsorted;
		if paramn = aa and bb then flag='Y';
		output;
		call missing(flag);
	end;
	drop count m n x aa bb;
run;

 I tried this, and it returns what youre looking for.

View solution in original post

0 Likes
Reply
6 REPLIES 6
nikcmo
Calcite | Level 5 nikcmo
Calcite | Level 5

Re: flag first record if there are consecutive same values by group

Posted 03-19-2024 01:59 PM (950 views)  |   In reply to chinna0369
What exactly do you mean when you say that the code is not working? Is it producing an error? Are you not flagging observations that you think should be flagged? If the flagging is not working as intended, can you give an example of an observation that should be flagged.
0 Likes
Reply
chinna0369
Pyrite | Level 9 chinna0369
Pyrite | Level 9

Re: flag first record if there are consecutive same values by group

Posted 03-19-2024 02:02 PM (940 views)  |   In reply to nikcmo
I have provided the data I have and I have provided the data I want. My code is flagging all the consecutive records and it is not checking paramn is consecutive or not.
0 Likes
Reply
nikcmo
Calcite | Level 5 nikcmo
Calcite | Level 5

Re: flag first record if there are consecutive same values by group

Posted 03-19-2024 02:33 PM (906 views)  |   In reply to chinna0369

I believe the issue is that variables in the PDV are not set to missing until you reach the end of the data step. As such, the value of flag is not going to be reset until you after you exit the second do until loop. 

 

Try doing 

if first.aval and m>4 and aval=0 then flag='Y';
else flag='';
0 Likes
Reply
chinna0369
Pyrite | Level 9 chinna0369
Pyrite | Level 9

Re: flag first record if there are consecutive same values by group

Posted 03-19-2024 03:00 PM (862 views)  |   In reply to nikcmo
Sorry, if I am confusing you. But my concern is not about first. or last. my concern is that it is not checking consecutive pramn is presented or not, which means my code flags all the consecutive record if there is any paramn is missing in between in same by group.
0 Likes
Reply
Mazi
Pyrite | Level 9 Mazi
Pyrite | Level 9

Re: flag first record if there are consecutive same values by group

Posted 03-19-2024 03:29 PM (838 views)  |   In reply to chinna0369 
proc sort data=have; by subjid parcat1 visit paramn aval; run;
data want2;
	do m=1 by 1 until(last.aval);
		set have;
		by subjid parcat1 visit aval notsorted;
		if first.aval then do;
			x=paramn; 
			n=1; 	
			count=1; 
		end;
		else if paramn = sum(x,n) then do; 
			n+1; 
			count+1; 
		end;
		else do;
			count=1;
			x=paramn;
			n=1;
		end;
		if count>4 then do;
			aa=x;
			bb=1;
		end;
	end; 
	do until(last.aval);
		set have;
		by subjid parcat1 visit aval paramn notsorted;
		if paramn = aa and bb then flag='Y';
		output;
		call missing(flag);
	end;
	drop count m n x aa bb;
run;

 I tried this, and it returns what youre looking for.

0 Likes
Reply
Quentin
Super User Quentin
Super User

Re: flag first record if there are consecutive same values by group

Posted 03-19-2024 03:30 PM (835 views)  |   In reply to chinna0369

I don't really like my approach, but I'll share it anyway in case it helps.  Also I couldn't understand the role of AVAL, so I ignored it.

 

The code you posted had tabs in the CARDS data, here is a a version with spaces:

data have;
input parcat1 $1-6 SUBJID $7-15 aval 16-17	paramn 18-21 visit $22-26;
datalines;
DAILY 10005501 0 301 V1
DAILY 10005501 1 302 V1
DAILY 10005501 0 303 V1
DAILY 10005501 1 304 V1
DAILY 10005501 0 305 V1
DAILY 10005501 2 306 V1
DAILY 10005501 2 307 V1
DAILY 10005501 2 308 V1
DAILY 10005501 0 309 V1
DAILY 10005501 2 310 V1
DAILY 10005501 0 311 V1
DAILY 10005501 0 312 V1
DAILY 10005501 1 313 V1
DAILY 10005501 0 314 V1
DAILY 10005501 1 315 V1
DAILY 10005501 2 316 V1
DAILY 10005501 1 317 V1
DAILY 10005501 2 318 V1
DAILY 10005501 0 319 V1
DAILY 10005501 0 320 V1
DAILY 10005501 0 321 V1
DAILY 10005501 0 322 V1
DAILY 10005501 2 323 V1
DAILY 10005501 0 324 V1
DAILY 10005501 0 346 V1
DAILY 10005501 0 347 V1
DAILY 10005501 0 348 V1
DAILY 10005501 0 349 V1
DAILY 10005501 0 350 V1
SOCIAL 10007501 1 301 V11
SOCIAL 10007501 2 303 V11
SOCIAL 10007501 2 304 V11
SOCIAL 10007501 0 305 V11
SOCIAL 10007501 0 306 V11
SOCIAL 10007501 2 307 V11
SOCIAL 10007501 0 308 V11
SOCIAL 10007501 0 309 V11
SOCIAL 10007501 2 310 V11
SOCIAL 10007501 0 311 V11
SOCIAL 10007501 0 312 V11
SOCIAL 10007501 2 313 V11
SOCIAL 10007501 0 314 V11
SOCIAL 10007501 0 315 V11
SOCIAL 10007501 0 316 V11
SOCIAL 10007501 0 317 V11
SOCIAL 10007501 0 319 V11
SOCIAL 10007501 0 320 V11
SOCIAL 10007501 0 321 V11
SOCIAL 10007501 0 322 V11
SOCIAL 10007501 0 323 V11
SOCIAL 10007501 0 325 V11
SOCIAL 10007501 0 326 V11
;
run;

First I used a DATA step to create a counter of sequential values of paramn, within subjid-parcat1-visit.  If there is a gap in paramn or if there is a change in subjid parcat1 visit, then I reset the counter to 1, and increment a seqid (which identifies a series of sequential values).

 

proc sort data=have;
  by subjid parcat1 visit paramn ; 
run;

data want1 ;
  set have ;
  by subjid parcat1 visit paramn ; 
  lagparamn=lag(paramn) ;

  if first.visit then do ;
    seq=1 ;
    seqid++1 ;
  end ;

  else if NOT first.visit then do ;
    if paramn-lagparamn=1 then seq++1 ;
    else do ;
      seq=1 ;
      seqid++1 ;
    end ;
  end ;
run ;

proc print data=want1 ;
  var subjid parcat1 visit paramn seqid seq; 
run ;

This returns:

 SUBJID     parcat1    visit    paramn    seqid    seq

10005501    DAILY       V1        301       1        1
10005501    DAILY       V1        302       1        2
10005501    DAILY       V1        303       1        3
10005501    DAILY       V1        304       1        4
10005501    DAILY       V1        305       1        5
10005501    DAILY       V1        306       1        6
10005501    DAILY       V1        307       1        7
10005501    DAILY       V1        308       1        8
10005501    DAILY       V1        309       1        9
10005501    DAILY       V1        310       1       10
10005501    DAILY       V1        311       1       11
10005501    DAILY       V1        312       1       12
10005501    DAILY       V1        313       1       13
10005501    DAILY       V1        314       1       14
10005501    DAILY       V1        315       1       15
10005501    DAILY       V1        316       1       16
10005501    DAILY       V1        317       1       17
10005501    DAILY       V1        318       1       18
10005501    DAILY       V1        319       1       19
10005501    DAILY       V1        320       1       20
10005501    DAILY       V1        321       1       21
10005501    DAILY       V1        322       1       22
10005501    DAILY       V1        323       1       23
10005501    DAILY       V1        324       1       24
10005501    DAILY       V1        346       2        1
10005501    DAILY       V1        347       2        2
10005501    DAILY       V1        348       2        3
10005501    DAILY       V1        349       2        4
10005501    DAILY       V1        350       2        5
10007501    SOCIAL      V11       301       3        1
10007501    SOCIAL      V11       303       4        1
10007501    SOCIAL      V11       304       4        2
10007501    SOCIAL      V11       305       4        3
10007501    SOCIAL      V11       306       4        4
10007501    SOCIAL      V11       307       4        5
10007501    SOCIAL      V11       308       4        6
10007501    SOCIAL      V11       309       4        7
10007501    SOCIAL      V11       310       4        8
10007501    SOCIAL      V11       311       4        9
10007501    SOCIAL      V11       312       4       10
10007501    SOCIAL      V11       313       4       11
10007501    SOCIAL      V11       314       4       12
10007501    SOCIAL      V11       315       4       13
10007501    SOCIAL      V11       316       4       14
10007501    SOCIAL      V11       317       4       15
10007501    SOCIAL      V11       319       5        1
10007501    SOCIAL      V11       320       5        2
10007501    SOCIAL      V11       321       5        3
10007501    SOCIAL      V11       322       5        4
10007501    SOCIAL      V11       323       5        5
10007501    SOCIAL      V11       325       6        1
10007501    SOCIAL      V11       326       6        2

Then can use PROC SQL to flag the records:

proc sql ;
  create table want2 as
  select *, seq=1 and max(seq)>=5 as Flag
  from want1
  group by seqid
  order by subjid, parcat1, visit, paramn
  ;
quit ;

returns:

 SUBJID     parcat1    visit    paramn    seqid    seq    Flag

10005501    DAILY       V1        301       1        1      1
10005501    DAILY       V1        302       1        2      0
10005501    DAILY       V1        303       1        3      0
10005501    DAILY       V1        304       1        4      0
10005501    DAILY       V1        305       1        5      0
10005501    DAILY       V1        306       1        6      0
10005501    DAILY       V1        307       1        7      0
10005501    DAILY       V1        308       1        8      0
10005501    DAILY       V1        309       1        9      0
10005501    DAILY       V1        310       1       10      0
10005501    DAILY       V1        311       1       11      0
10005501    DAILY       V1        312       1       12      0
10005501    DAILY       V1        313       1       13      0
10005501    DAILY       V1        314       1       14      0
10005501    DAILY       V1        315       1       15      0
10005501    DAILY       V1        316       1       16      0
10005501    DAILY       V1        317       1       17      0
10005501    DAILY       V1        318       1       18      0
10005501    DAILY       V1        319       1       19      0
10005501    DAILY       V1        320       1       20      0
10005501    DAILY       V1        321       1       21      0
10005501    DAILY       V1        322       1       22      0
10005501    DAILY       V1        323       1       23      0
10005501    DAILY       V1        324       1       24      0
10005501    DAILY       V1        346       2        1      1
10005501    DAILY       V1        347       2        2      0
10005501    DAILY       V1        348       2        3      0
10005501    DAILY       V1        349       2        4      0
10005501    DAILY       V1        350       2        5      0
10007501    SOCIAL      V11       301       3        1      0
10007501    SOCIAL      V11       303       4        1      1
10007501    SOCIAL      V11       304       4        2      0
10007501    SOCIAL      V11       305       4        3      0
10007501    SOCIAL      V11       306       4        4      0
10007501    SOCIAL      V11       307       4        5      0
10007501    SOCIAL      V11       308       4        6      0
10007501    SOCIAL      V11       309       4        7      0
10007501    SOCIAL      V11       310       4        8      0
10007501    SOCIAL      V11       311       4        9      0
10007501    SOCIAL      V11       312       4       10      0
10007501    SOCIAL      V11       313       4       11      0
10007501    SOCIAL      V11       314       4       12      0
10007501    SOCIAL      V11       315       4       13      0
10007501    SOCIAL      V11       316       4       14      0
10007501    SOCIAL      V11       317       4       15      0
10007501    SOCIAL      V11       319       5        1      1
10007501    SOCIAL      V11       320       5        2      0
10007501    SOCIAL      V11       321       5        3      0
10007501    SOCIAL      V11       322       5        4      0
10007501    SOCIAL      V11       323       5        5      0
10007501    SOCIAL      V11       325       6        1      0
10007501    SOCIAL      V11       326       6        2      0

I'll look forward to better answers from other folks.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Reply

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • ‎03-19-2024 01:05 PM
  • 979 views
  • 1 like
  • 4 in conversation