topic Re: count positive and neative values in a series in SAS Data Management

count positive and neative values in a series

cd2011 — Thu, 14 Apr 2016 21:41:47 GMT

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.

Re: count positive and neative values in a series

Astounding — Thu, 14 Apr 2016 22:47:09 GMT

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;

Re: count positive and neative values in a series

Haikuo — Thu, 14 Apr 2016 22:56:37 GMT

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;
;

Re: count positive and neative values in a series

Ksharp — Fri, 15 Apr 2016 07:00:08 GMT

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;

Re: count positive and neative values in a series

cd2011 — Fri, 15 Apr 2016 14:00:37 GMT

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.

Re: count positive and neative values in a series

ballardw — Fri, 15 Apr 2016 15:00:30 GMT

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.