What is PROC FREQ equivalent in SQL?

Question

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.

Answer 1

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

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