array question:

[deleted_user]

Hi Folks,
I have an excel with 5 columns( ID, Visit, Time, Data2, Marker). Marker has a "Mark" tag depending on the analysis. My requirement here is to keep 3 records from the third row once I see "Mark" tag in the marker variable. I used this code

Data a;set b;
retain marker_flg marker_cnt;
marker_flg=0;marker_cnt=0;
if marker_flg=0 and index(upcase(marker),'MARK') then do;
marker_cnt=1;
marker_flg=1;
end;
else if marker_flg=1 & index(upcase(marker),'MARK')=0 then do;
marker_cnt=marker_cnt+1;
if marker_cnt>=3 and marker_cnt<6 then do;
length test_type $ 20;
test_type='2.5min';
output;
end;
Run;

When I use this code I was able to successfully pull the desired 3 rows. But in few excel files I have a mark flag in the marker variable in between. here is an example

ID VI Time Data1 Marker
1 1 10:30 11.2
1 1 11:00 12 Mark
1 1 11:30 11
1 1 12:00 10
1 1 12:30 11
1 1 01:00 12 *Mark*
1 1 01:30 11
1 1 02:00 10

In this example if I use the above code I can successfully keep records with times 12:00,12:30 and 01:00. My question is what should I do if I have an other MARK tag at 01:00 in the marker variable and I want to just keep two rows ( 12:00 and 12:30)? As I read multiple excel files from a folder I don't know which once have a MARK tag in between.

Any help is greatly appreciated.

Regards,
Matt

[Patrick]

Hi Matt

Not sure if this handels the cases the way you need it - but it might give you an idea how to approach the problem:

data have;
infile datalines truncover;
input ID VI Time time5. Data1 Marker $;
format time time5.;
datalines;
1 1 10:30 11.2
1 1 11:00 12 Mark
1 1 11:30 11 mark
1 1 12:00 10 mark
1 1 12:30 11
1 1 01:00 12 *Mark*
1 1 01:30 11
1 1 02:00 10
1 1 02:30 10 mark
1 1 03:00 10
1 1 03:30 10
1 1 04:00 10
1 1 04:30 10
1 1 05:00 10 mark
1 1 05:30 10
1 1 06:00 10
1 1 06:30 10
1 1 07:00 10
1 1 07:30 10
1 1 08:00 10
run;

data want;
set have;
by ID VI;
retain flag counter;
if first.id or counter>=5 then
do;
flag=0;
counter=0;
end;

flag= ( sum(find(Marker,'mark','i')>0,flag)=1 );

if ( find(Marker,'mark','i') and counter<3 ) then
do;
flag=1;
counter=0;
end;

counter=sum(counter,1)*flag;

if counter GE 3 and counter LE 5 then output;

put flag= counter= marker=;
run;

proc print data=want;
run;

HTH
Patrick