count positive and neative values in a series

[cd2011]

Hi,

I am trying to do something like this, but stuck at this point.

I have a data shown below - I want to know how many negative values appear consistently. Ultimately I decide if several negative values appear consistently I want to keep them, otherwise leave. I dont use the positive values. I am able to get the counts of consequitive negative values, column "count". But I would like a column like "totct", which I am unable to calculate yet. As you can see there is no common id to do a group ct and them merge.

Any help will be highly appreciated.

date                         value              count      Totct

3/1/2016 4:55	-100	1	5
3/1/2016 5:00	-120	2	5
3/1/2016 5:05	-50	3	5
3/1/2016 5:10	-400	4	5
3/1/2016 5:15	-250	5	5
3/1/2016 5:35	45	0	0
3/1/2016 5:40	58	0	0
3/1/2016 5:45	-80	1	1
3/1/2016 5:50	4	0	0
3/1/2016 5:55	-52.8	1	4
3/1/2016 6:00	-150	2	4
3/1/2016 6:05	-400	3	4
3/1/2016 6:10	-42	4	4

Thanks a bunch.

[Astounding]

Here's a way that assumes your original data set is in sorted order BY DATE.  If that's not actually the case, we might have to revisit the problem.

proc sort data=have;

by descending date;

run;

data want;

set have;

if count =0 then TotCnt=0;

else TotCnt = max(TotCnt, Count);

retain TotCnt;

run;

Optionally, put the data set back into its original order:

proc sort data=want;

by date;

run;

[Haikuo]

Another approach, first introduct a new variable 'sign' to mark the sign of the value, then 2X DOW get the job done:

data have;
	infile cards truncover expandtabs;
	input date  anydtdtm13.  value;
	format date datetime25.2;
	sign=ifn(value < 0,-1,0);
	cards;
3/1/2016 4:55 -100 
3/1/2016 5:00 -120 
3/1/2016 5:05 -50 
3/1/2016 5:10 -400 
3/1/2016 5:15 -250 
3/1/2016 5:35 45  
3/1/2016 5:40 58  
3/1/2016 5:45 -80 
3/1/2016 5:50 4  
3/1/2016 5:55 -52.8
3/1/2016 6:00 -150 
3/1/2016 6:05 -400 
3/1/2016 6:10 -42 
;

data want;
	do totct=1 by 1 until (last.sign);
		set have;
		by sign notsorted;
	end;

	if sign > -1 then
		totct=0;

	do count=1 by 1 until (last.sign);
		set have;
		by sign notsorted;
		output;
	end;
run;
;

[Ksharp]

data have;
	infile cards truncover expandtabs;
	input date  anydtdtm13.  value;
	format date datetime25.2;
	cards;
3/1/2016 4:55 -100 
3/1/2016 5:00 -120 
3/1/2016 5:05 -50 
3/1/2016 5:10 -400 
3/1/2016 5:15 -250 
3/1/2016 5:35 45  
3/1/2016 5:40 58  
3/1/2016 5:45 -80 
3/1/2016 5:50 4  
3/1/2016 5:55 -52.8
3/1/2016 6:00 -150 
3/1/2016 6:05 -400 
3/1/2016 6:10 -42 
;
run;
data temp;
 set have;
 if value lt 0 then count+1;
  else count=0;
 if count in (0 1) then group+1;
run;
data want;
 do until(last.group);
  set temp;
  by group;
 end;
 tot=count;
 do until(last.group);
  set temp;
  by group;
  output;
 end;
 drop group;
run;

[cd2011]

Last two suggestions worked perfectly, the first one did not work on the first attempt and I did not try again. Thanks much for all the suggestions, saved  me a lot of time.

[ballardw]

Please select one of the working solutions and idicate it as your preferred solution. Other people using this forum will then be able to tell that your question was answered in a suitable fashion.