BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JinboZhao
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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
Calcite | Level 5

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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