topic Count Zeroes Values That Follow Non-Zero Values in SAS Data Science

Count Zeroes Values That Follow Non-Zero Values

Filoplume — Mon, 22 Jul 2024 11:25:14 GMT

Hi,

An absolute noob here. I want to be able to count the number of zeroes per row (in this case stores) that follows non- zero values.

However, I am unable to run the macro get the data step of my dataset, so apologies for the data sample below:

data WORK.CLASS(label='Store Data'); infile datalines dsd truncover; input Store:$8. Mon_01:comma18.6 Mon_02:comma18.6 Mon_03:comma18.6 Mon_04:comma18.6; datalines; 123 373 373 374 375 297 0 0 0 0 1256 0.032 0.013 0 0.012 5042 34 0 62 0 9880 .9 .89 0 0 ;;;;

I have tried multiplying the columns with the previous columns (Mon_01 * Mon_02, Mon_02 * Mon_03) , but somehow it does not work and I know that there is a better way to count the zeroes per store.

Thanks in advance.

Filoplume

Re: Count Zeroes Values That Follow Non-Zero Values

Astounding — Mon, 22 Jul 2024 11:55:33 GMT

Believe it or not, there is some question as to what you are asking for here. I'm going with the title: zeros that follow nonzeros.

Here's one way:

data want;
   set have;
   array counts {4} Mon_01 - Mon_04;
   zeros = 0;
   do n=2 to 4;
      if counts{n} = 0 and counts{n-1} ne 0 then zeros + 1;
   end;
run;

Re: Count Zeroes Values That Follow Non-Zero Values

Kurt_Bremser — Mon, 22 Jul 2024 12:01:28 GMT

Don't keep data in structure.

proc transpose data=class out=class_long;
by store;
var mon_:;
run;

data want;
do until (last.store);
  set class_long;
  by store;
  count = sum(count,(lag(col1) ne 0 and col1 = 0 and not first.store));
end;
keep store count;
run;

With the wide structure, use array processing:

data want;
set class;
array mon {*} mon_:;
do i = 2 to dim(mon);
  count = sum(count,(mon{i-1} ne 0 and mon{i} = 0));
end;
drop i;
run;