SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

count positive and neative values in a series

Reply
Contributor
Posts: 39

count positive and neative values in a series

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.

Super User
Posts: 5,509

Re: count positive and neative values in a series

[ Edited ]

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;

Respected Advisor
Posts: 3,156

Re: count positive and neative values in a series

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;
;
Super User
Posts: 10,029

Re: count positive and neative values in a series

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;
Contributor
Posts: 39

Re: count positive and neative values in a series

[ Edited ]

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.

 

 

Super User
Posts: 11,343

Re: count positive and neative values in a series

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.

Ask a Question
Discussion stats
  • 5 replies
  • 574 views
  • 2 likes
  • 5 in conversation