Counting observations with a specific value

[AlG]

Hi experts,

I have a pretty basic question.

I have a panel dataset, including weekly data on several firms. I have a variable "Week_Volume". I want to know for each firm (identifier variable: brand_name) how many times (i.e., for how many weeks) "Week_Volume" equals zero. 

Can you please recommend an easy solution for this? Thanks!

[ballardw]

Proc freq data=have;

   where week_volume = 0;

   tables brandname;

run;

Assumes that you only have one week_volume value per week.

[AlG]

@ballardw Thanks. Based on what you said, I tried the following code. 

Proc freq data=Final;

   where Week_Volume = 0;

   tables brand_name;

run;

I get the following error:

657  Proc freq data=Final;
658
659     where Week_Volume = 0;
ERROR: WHERE clause operator requires compatible variables.
660
661     tables brand_name;
662
663  run;

I thought maybe this is because the type of variable Week_Volume. So I ran this code:

data Final;                                                                                                                                                                                                                                                     
   y=vtype(Week_Volume);                                                                                                                         
   put y=;                                                                                                                                
run; 

It says Week_Volume=N

[ballardw]

---

@AlG wrote:

@ballardw Thanks. Based on what you said, I tried the following code. 

Proc freq data=Final;

   where Week_Volume = 0;

   tables brand_name;

run;

I get the following error:

657  Proc freq data=Final;
658
659     where Week_Volume = 0;
ERROR: WHERE clause operator requires compatible variables.
660
661     tables brand_name;
662
663  run;

I thought maybe this is because the type of variable Week_Volume. So I ran this code:

data Final;                                                                                                                                                                                                                                                     
   y=vtype(Week_Volume);                                                                                                                         
   put y=;                                                                                                                                
run; 

It says Week_Volume=N

---

Try retyping the zero in the where clause. The forum seems to have changed a simple 0 to one with the slash when copy and pasted from the forum.

[AlG]

@ballardw I still get the same error...

Could it be an issue with the variable Week_Volume? However, I have no clue what may be wrong with it...

[Reeza]

This doesn't make sense so there's something else going on that's not being shown. 

Please post the full code you ran and your full log. 

Here's an example showing this should work correctly, if everything is as you specified.

data demo;
set sashelp.class;
if age in (13, 14, 11) then flag=1;
else flag=0;

x =vtype(flag);
put x;
run;

Proc freq data=demo;

   where flag = 0;

   tables name;

run;

---

@AlG wrote:

@ballardw Thanks. Based on what you said, I tried the following code. 

Proc freq data=Final;

   where Week_Volume = 0;

   tables brand_name;

run;

I get the following error:

657  Proc freq data=Final;
658
659     where Week_Volume = 0;
ERROR: WHERE clause operator requires compatible variables.
660
661     tables brand_name;
662
663  run;

I thought maybe this is because the type of variable Week_Volume. So I ran this code:

data Final;                                                                                                                                                                                                                                                     
   y=vtype(Week_Volume);                                                                                                                         
   put y=;                                                                                                                                
run; 

It says Week_Volume=N

---

[ballardw]

Provide an example of the Final dataset you are using as data step code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Pasting the generated code into a code box is important as the reformatting the message windows do can render data step code that will not run.

[AlG]

I tried to carefully follow the instructions provided on that page and here is what I got:

data WORK.FINAL;
  infile datalines dsd truncover;
  input brand_name:$10. Year0:BEST12. week:BEST12. Week_Volume:BEST12.;
  format Year0 BEST12. week BEST12. Week_Volume BEST12.;
datalines;
Absolut 2014 6 4
Absolut 2014 7 0
Absolut 2014 8 0
Absolut 2015 0 2
Absolut 2015 1 6
Absolut 2015 2 3
Aeromexico 2014 6 0
Aeromexico 2014 7 0
Aeromexico 2014 8 0
Aeromexico 2014 9 0
Aeromexico 2015 0 2
Aeromexico 2015 1 8
Aeromexico 2015 2 8
Air Canada 2014 6 29
Air Canada 2014 7 34
Air Canada 2014 8 25
Air Canada 2015 0 0
Air Canada 2015 1 25
Air Canada 2015 2 18
Air France 2014 6 4
Air France 2014 7 2
Air France 2014 8 5
Air France 2015 0 1
Air France 2015 1 3
Air France 2015 2 2
;;;;

[Barkat]

Here you go

data FINAL;
infile datalines dsd dlm=" ";
input brand_name :$10. Year0 :4. week :2. Week_Volume :2.;
format Year0 4. week 2. Week_Volume 2.;
datalines;
Absolut 2014 6 4
Absolut 2014 7 0
Absolut 2014 8 0
Absolut 2015 0 2
Absolut 2015 1 6
Absolut 2015 2 3
Aeromexico 2014 6 0
Aeromexico 2014 7 0
Aeromexico 2014 8 0
Aeromexico 2014 9 0
Aeromexico 2015 0 2
Aeromexico 2015 1 8
Aeromexico 2015 2 8
Air Canada 2014 6 29
Air Canada 2014 7 34
Air Canada 2014 8 25
Air Canada 2015 0 0
Air Canada 2015 1 25
Air Canada 2015 2 18
Air France 2014 6 4
Air France 2014 7 2
Air France 2014 8 5
Air France 2015 0 1
Air France 2015 1 3
Air France 2015 2 2
;
run;

data final1; set final;
where Week_Volume=0;
run;

proc means data=final1 n; var Week_Volume; by brand_name; run;

[AlG]

@Barkat I get the same error. It says:

3641  data final1; set final;
3642  where Week_Volume=0;
ERROR: WHERE clause operator requires compatible variables.
3643  run;

[Barkat]

I did not get any errors. Please restart your SAS and copy the whole thing from my answer and run.

[AlG]

Thanks @Barkat 

When I copy past your whole code it works.

However, I am a bit confused here. This is just a small fraction of my dataset. My dataset has 27k rows and includes 110 different firms. So my question is when I import that dataset (using proc import), what should I do next? Thanks again.

[Barkat]

In your code replace truncover with dlm=" " then try

[Barkat]

The following statement works as well. You will not need to create any subset.

In your code replace truncover with dlm=" "

You will not get any error.

proc freq data=final; where Week_Volume=0; table brand_name; run;

[AlG]

Sorry, I don't get what you are saying 😞