I have been using proc Freq for more than 30 plus years, and it never failed me until this past week.

I am dealing with a new project which requires me to create a Data profile for a variety of MS SQL tables creating such stats as nmiss, min, mean, max, range, etc for every variable in the SQL table.

Well, I found out that Proc Freq failed when I encountered a variable with more than 17 million unique values and I did not have enough memory on my SAS Grid environment to create "count and percent" for this variable.

It would be nice to have an option within Proc Freq to get these stats but in a slower manner when necessary. Instead, I had to do a Proc sort and then process the data twice to do my own count.

Then because this was taking so long to sort the dataset when I had numerous variables, I implemented a conditional macro to check to see if the "count (distinct "&single_variable)" was greater than 15 million values ( I arbituarily decied on 15 instead of 17 million) and go back to sorting the dataset for any of those variables which had more levels than 15 million.

So, if you can persuade Proc FREQ R&D people to handle any size number of levels for a one-way FREQ table, I would appreciate that.

Just to let you know, my Data profile for each table and each variable looks like this - it would be nice to have a proc that would do this automatically:

Library Name Member Name Member Type DBMS Member Type Data Set Label Data Set Type Date Created Date Modified Number of Physical Observations Observation Length Number of Variables Type of Password Protection Compression Routine Encryption Number of Pages Size of File Percent Compression Reuse Space Bufsize Number of Deleted Observations Number of Logical Observations Longest variable name Longest label Maximum number of generations Generation number Data Set Attributes Type of Indexes Data Representation Name of Collating Sequence Sorting Type Charset Sorted By Requirements Vector Data Representation Name Data Encoding Audit Trail Active? Audit Before Image? Audit Admin Image? Audit Error Image? Audit Data Image? Number of Character Variables Number of Numeric Variables
JDE_PATH EDM_CLAIMS_2010_APLUSPROP DATA DATA 18SEP14:13:55:26 18SEP14:13:55:26 11928602 713 35 --- CHAR NO 398363 3263397888 63 no 8192 0 11928602 24 24 0 . ON NATIVE 181F101133220033330102310133012333001D0000200301 SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64 latin1 Western (ISO) no no no no no 27 8
LibPath table_name Num_Obs Var_Name Var_Label Var_Type Var_Length Var_Format Var_InFmt _Var_Num_ nmiss distinct_cnt min mean max range sum _TOP_1 _TOP_2 _TOP_3 _TOP_4 _TOP_5 __RECORD__1 __RECORD__2 __RECORD__3 __RECORD__4 __RECORD__5 __RECORD__6 __RECORD__7 __RECORD__8 __RECORD__9 __RECORD__10
MS_SQL_TABLE 11928602 ALLCLAIMKEY ALLCLAIMKEY N 8 20 20 1 0 11928602 1 5964301.5 11928602 11928601 7.11458E+13 1 | 1 2 | 1 3 | 1 4 | 1 5 | 1 1 2 3 4 5 6 7 8 9 10

Regards,
A Long Time SAS User - like 37+ years

Charles Patridge