I want to use the functionality that PROC FREQ provides in SAS. PROC FREQ helps in giving the statistics such as frequency, percent, cumulative frequency etc. of a variable or variables. The link attached explains the function in more detail. I am aware that I can use PIVOT in SQL Server to achieve somewhat the same objective. However, there are certain feature of PROC FREQ that I cannot find SQL equivalent. To be precise:

table x/norow nocol nocum nopercent missing

https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_freq_sect010.htm

This link helped me figure out that table is basically a cross tabulation technique, so how can I use pivot in SQL for this? Next, the missing treats missing values as non-missing. How can I do the same in SQL?

Any thoughts/advice/help much appreciated.

  • Isnt no percent one word? --> nopercent – Randi Vertongen Nov 8 at 12:11
  • yes it is, thanks – T.H. Nov 8 at 13:36
  • 1
    The question assumes knowledge of what SAS' PROC FREQ, whatever that is, does. Please consider explaining what you want to achieve in human language. – mustaccio Nov 8 at 13:38
  • Apologies, have briefly explained and attached a link for detail. – T.H. Nov 8 at 16:20
up vote 1 down vote accepted

I recreated a SAS PROQ FREQ Missing values example due to lack of more information.

Example: https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_freq_sect016.htm

Sas data set:

   data one;
      input A Freq;
      datalines;
   1 2
   2 2
   . 2
   ;

Sas Query:

   proc freq data=one;
      tables A / missing;
      weight Freq;
      title 'MISSING Option';
   run;

Sas result set

enter image description here

SQL Server Data Set --> Using two tables to number duplicate missing values

DECLARE  @A TABLE (id INT,
      Freq INT);
      DECLARE  @B TABLE (id INT,
      Freq INT,
      rownum int);

      INSERT INTO @A
   VALUES( 1, 2),
   (2,2),
   (NULL, 2),
   (NULL, 2)
   ;
   insert into  @B(rownum,id,Freq)
   select ROW_NUMBER() OVER (ORDER BY  id) , id, freq 
   from @A

SQL Server Query: (compatible with SQL Server 2008)

SELECT   B.id,
B.Freq as Frequency,
        (cast(B.Freq as DECIMAL(2,1)) / cast(sum(B2.freq) over() as DECIMAL(2,1)))  * 100 as Percentage ,
        sum(B2.Freq) as cumulative_frequency, 
        ( (cast(sum(B2.freq) as DECIMAL(2,1))) / cast(B.Freq as DECIMAL(2,1)) *2) / cast(sum(B2.freq) over() as DECIMAL(2,1))  * 100 as CumulativePercentage  
FROM @B B
inner join @B B2 on B.rownum >= B2.rownum
GROUP by B.rownum,B.id,B.Freq,B2.Freq

SQL Server result set:

id      Frequency   Percentage  cumulative_frequency    CumulativePercentage
NULL    2          25.000000    2                       25.000000000
NULL    2          25.000000    4                       50.000000000
1   2              25.000000    6                       75.000000000
2   2              25.000000    8                       100.000000000

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.