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 |
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.
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;
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?
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.