Count specific values frequency for each variable in one dataset

[JinboZhao]

Hi SAS,

I have a table with over 20 variables, for each variable it contains many same values like -9999997.00. I want to use some coding to count the frequency of this value in each variable.

I can do once a time use

proc freq data=want (where=(var1=-9999997.00));

tables var1/out = want1;

run;

But it is time-consuming to duplicate this process all the time. Could anyone give some clue to slove it easier? Thank you. 

The following is part of my dataset:

Var1	Var2	Var3	Var4	Var5	Var6	Var7
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
25.0	155.00	125.00	2.00	2758	1650	1.93
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
25.0	443.00	443.00	1.00	5349	4233	4.48
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
25.0	174.00	174.00	1.00	3499	3429	1.18
25.0	7.00	7.00	1.00	28	15	0.29
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
25.0	240.00	210.00	1.00	1114	2700	24.91
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
25.0	104.00	104.00	1.00	1092	1048	3.01
25.0	104.00	104.00	1.00	1092	1048	3.01
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
25.0	130.00	130.00	1.00	3275	3246	2.43
25.0	68.00	68.00	0.00	2087	125	9.42
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00
-9999997.0	-9999997.00	-9999997.00	-9999997.00	-9999997	-9999997	-9999997.00

[Reeza]

Use a custom format and apply that to all your variables and then run a proc freq.

Untested, but hopefully gives you the idea:

proc format;
value myFmt
-999999997 = 'Large Negative'
other = 'Other Values';
run;

proc freq data=have;
table var1-var7;
format var1-var7 myfmt.;
run;

[JinboZhao]

Hi, after I tried, it shows 

!  -9999997.00 = 'Large neg'
_____________
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
26 other ='other';

Could you give me further suggestion? 

[Reeza · Accepted solution]

The idea is correct, please look up how to create a format.

http://www2.sas.com/proceedings/sugi30/001-30.pdf

You can use the method that they use for identifying missing, except you have a specific value you're trying to filter. Without data to test, I'd have to generate sample data and then write the code and I'm too lazy to do that right now. This is why if you provide sample data as a data step you're more likely to get better answers.