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

Count specific values frequency for each variable in one dataset

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Count specific values frequency for each variable in one dataset

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:

Var1Var2 Var3Var4Var5Var6Var7
-9999997.0-9999997.00-9999997.00-9999997.00-9999997-9999997-9999997.00
25.0155.00125.002.00275816501.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.0443.00443.001.00534942334.48
-9999997.0-9999997.00-9999997.00-9999997.00-9999997-9999997-9999997.00
25.0174.00174.001.00349934291.18
25.07.007.001.0028150.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.0240.00210.001.001114270024.91
-9999997.0-9999997.00-9999997.00-9999997.00-9999997-9999997-9999997.00
25.0104.00104.001.00109210483.01
25.0104.00104.001.00109210483.01
-9999997.0-9999997.00-9999997.00-9999997.00-9999997-9999997-9999997.00
25.0130.00130.001.00327532462.43
25.068.0068.000.0020871259.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

Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 17,829

Re: Count specific values frequency for each variable in one dataset

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.

View solution in original post


All Replies
Super User
Posts: 17,829

Re: Count specific values frequency for each variable in one dataset

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

Re: Count specific values frequency for each variable in one dataset

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? 

Solution
4 weeks ago
Super User
Posts: 17,829

Re: Count specific values frequency for each variable in one dataset

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 323 views
  • 0 likes
  • 2 in conversation