topic Re: Counting observations with a specific value in New SAS User

Counting observations with a specific value

AlG — Thu, 16 Jul 2020 20:35:13 GMT

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!

Re: Counting observations with a specific value

ballardw — Thu, 16 Jul 2020 20:38:39 GMT

Proc freq data=have;

where week_volume = 0;

tables brandname;

run;

Assumes that you only have one week_volume value per week.

Re: Counting observations with a specific value

Barkat — Thu, 16 Jul 2020 20:59:15 GMT

You may want to try something as below.

if Week_Volume1 =0 then do; count1=1; end;
if Week_Volume2 =0 then do; count2=1; end;
if Week_Volume3 =0 then do; count3=1; end;
if Week_Volume4 =0 then do; count4=1; end;
if Week_Volume5 =0 then do; count5=1; end;
if Week_Volume6 =0 then do; count6=1; end;
if Week_Volume7 =0 then do; count7=1; end;

zero_vol_wk = sum (of count1 - count7);

drop count1 - count7;

Re: Counting observations with a specific value

AlG — Thu, 16 Jul 2020 21:12:09 GMT

@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

Re: Counting observations with a specific value

AlG — Thu, 16 Jul 2020 21:15:28 GMT

Thanks @Barkat . However, I don't get your code.

I have just one Week_Volume variable, but your code shows Week-Volume 1-7. I don't have such variables.

Re: Counting observations with a specific value

ballardw — Thu, 16 Jul 2020 21:29:54 GMT

@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.

Re: Counting observations with a specific value

Barkat — Thu, 16 Jul 2020 21:34:00 GMT

My bad!

Never mind. I thought you have data from many weeks.

Re: Counting observations with a specific value

AlG — Thu, 16 Jul 2020 21:37:01 GMT

@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...

Re: Counting observations with a specific value

AlG — Thu, 16 Jul 2020 21:36:22 GMT

@Barkat

I do! I have 54 weeks for each firm, but just one column (week_volume), which indicates the volume of activity in a given week for a given firm.

Re: Counting observations with a specific value

Reeza — Thu, 16 Jul 2020 22:02:59 GMT

@Barkat given no data was provided that's a fair assumption and a good example of why sample data is important. It also helps avoid issue with types and formats, since if you use the automated data set builders suggested it uses the correct formats/types applied.

Re: Counting observations with a specific value

Barkat — Thu, 16 Jul 2020 22:06:16 GMT

Could you provide a sample data, please.

Re: Counting observations with a specific value

Reeza — Thu, 16 Jul 2020 22:06:27 GMT

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

Re: Counting observations with a specific value

ballardw — Thu, 16 Jul 2020 22:21:08 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Counting observations with a specific value

Barkat — Thu, 16 Jul 2020 22:53:00 GMT

You may want to create a subset where wkvol=0. Then do proc means by brand name.

see below;

data hv;
infile datalines dsd dlm=',' ;
input name :$3. wkvol 5.;
datalines;
abc,0
def,023
pqr,125
xyz,200
jkl,0
jkl,0
jkl,280
run;

data hv1;

set hv;

where wkvol=0;

run;

proc sort data=hv1;

by name;

run;

proc means data =hv1 n;

var wkvol;

by name;

run;

Re: Counting observations with a specific value

AlG — Thu, 16 Jul 2020 23:07:11 GMT

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

Re: Counting observations with a specific value

Barkat — Thu, 16 Jul 2020 23:32:00 GMT

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;

Re: Counting observations with a specific value

AlG — Thu, 16 Jul 2020 23:38:46 GMT

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

Re: Counting observations with a specific value

Barkat — Thu, 16 Jul 2020 23:43:08 GMT

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

Re: Counting observations with a specific value

AlG — Thu, 16 Jul 2020 23:49:29 GMT

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.

Re: Counting observations with a specific value

Barkat — Fri, 17 Jul 2020 00:09:00 GMT

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;