0

I want to count the number of provider id numbers (CLM_RNDRG_PRVDR_NPI_NUM) that are associated with each level for HCPCS and SPECIALTY CODE. However, my output displays each provider ID (001 TO 020) across columns. After the "Allowed Units" column, I want the "Providers" column to count the number of providers for each HCPCS CODE. 

proc tabulate data=&ds    order=freq;
    class         clm_line_hcpcs_cd clm_rndrg_fed_prvdr_spclty_cd CLM_RNDRG_PRVDR_NPI_NUM;        
    var           clm_line_alowd_chrg_amt  clm_line_cvrd_pd_amt clm_line_prvdr_pmt_amt  
                  CLM_LINE_ALOWD_UNIT_QTY;
    table        
    /***Rows***/
       clm_line_hcpcs_cd='HCPCS'
       clm_rndrg_fed_prvdr_spclty_cd='SPECIALTY CODE'
    ,
    /***Columns***/
     all=''*(n='LINES'*format=comma30. pctn='%') 
         clm_line_alowd_unit_qty=''*(sum='Allowed Units'*format=comma30. pctsum='%')
         CLM_RNDRG_PRVDR_NPI_NUM=''*(n='Providers'*format=comma30. colpctn='%')
         clm_line_alowd_chrg_amt=''*(sum='Allowed Payments'*format=dollar30. pctsum='%')
         clm_line_cvrd_pd_amt=''*(sum='Covered Payments'*format=dollar30. pctsum='%')
         clm_line_prvdr_pmt_amt=''*(sum='Provider Payments'*format=dollar30. pctsum='%')
       /misstext='0.00' row=float box='Lines and Payments by HCPCS & Specialty';
      title1 'Line-Level Summary';
      title2 'All Claims';
     run;title;footnote;

HERE'S THE ERRONEOUS OUTPUT:

enter image description here

Line-Level Summary All Claims

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
nhandy
  • 73
  • 7
  • 1
    Can your reproduce the problem to some simple sample data ? There is no statistic for variablea that counts the number of distinct values within an aggregate cell (class{or dimensional} crossing). You might have to preprocess the data, say with `SQL` and `select distinct` grouped by the desired crossing into a variable that is used in tabulate with the `min` or `max` statistic (the precomputed count distinct will be static over the aggregate, so a min or max stat will not alter that value) – Richard Nov 18 '19 at 22:35
  • If you're trying to do a count distinct of your ID's unfortunately that's not possible, if you just need a frequency, that's pretty trivial. – Reeza Nov 18 '19 at 23:16
  • If you can pose a question based on one of the standard sample datasets in the `SASHELP` library, we can all try out our answers before posting them. – Dirk Horsten Nov 19 '19 at 08:42

0 Answers0