BookmarkSubscribeRSS Feed
Pyrite | Level 9


I am trying to print multiple variables generated from my proc freq so that I can choose the first 20 highest row. I am attempting to use macro but am not getting the expected results. This is my code without macro

proc freq data=icd10_06_15 order=freq noprint;
table diag1--diag12/ out=ao nocum nocol;
proc print data=ao (obs=20) noobs;

Code with macro

%macro around(start_diag=,end_diag=);
    %do diag=&start_diag %to &end_diag;
        proc freq data=icd10_06_15 order=freq noprint;
 table diag1--diag12/ out=ao nocum nocol;
proc print data=ao (obs=20) noobs; run;

In my output, sas did not generate the diag1-diag11 but it only generate diag12 but I want all the diag variable from diag1 to diag12 in my output dataset AO so I can use proc print to select the first 20 obs.


Pyrite | Level 9

@Kurt_Bremser  this is how the data is set up


Your macro is just generating this code 12 times:

proc freq data=icd10_06_15 order=freq noprint;
table diag1--diag12/ out=ao nocum nocol;
proc print data=ao (obs=20) noobs;

When you run the PROC FREQ step, it only generates one output dataset ao with the frequency diag12, because that is the way the OUT= option works on the table statement.  You only get one output dataset, for the last table specified.


You could use the macro language to generate a step with 12 table statements like:

proc freq data=icd10_06_15 order=freq noprint;
table diag1/ out=ao1 nocum nocol;
table diag2/ out=ao2 nocum nocol;
table diag3/ out=ao3 nocum nocol;
/*... */

data a0;
  set a01-a012;

But you still end up with 12 variables in ao with the count of each diagnosis variables.  If you were happy with that, you don't need a macro, you could use:

ods output OneWayFreqs=ao ;
proc freq data=have order=freq ;
  table diag1-diag12/ out=ao1 nocum nocol;
run ;
ods output close ;

But as @Kurt_Bremser mentioned, if you transpose your data first to a long format, you will make this task, and many more, much much easier.  One of the great strengths of SAS is that it makes it easy to reshape data into a format that makes data analysis easier.  While many times people think in terms of "one row per patient," if you use this sort of data structure, the coding becomes much more time consuming and error-prone.

Check out the Boston Area SAS Users Group (BASUG) video archives:
Super User

Try this instead:


  • ods select none;
    ods output onewayfreqs=temp;
    proc freq data=icd10_06_15;
    table diag1--diag12;
    ods select all;
    *Format output;
    data temp2;
    length variable $32. variable_value $50.;
    set temp;
    Variable=scan(table, 2);
    keep variable variable_value frequency percent cum:;
    label variable='Variable' 
        variable_value='Variable Value';
    proc print data=temp2(obs=20) label;
    proc freq data=temp2;
    table value / out=ao;
    weight frequency;
  • Use PROC FREQ to generate summary for each variable into a table
  • Use a Data Step to clean up the format of the output data to make it easier to summarize
  • Summarize it again to get the diagnosises overall, regardless of code

Not sure if you need temp2 or ao as your output. 


Note: This solution does not handle the case where you may have a diagnosis multiple times for a single individual in a single row. 

Super User Tom
Super User

You would have to generate separate TABLES statements and separate output dataset names.

If you want the DIAG1 to DIAG12 to end up in the same variable in the output then use the RENAME= dataset option when making the datasets.

%macro around(start_diag=,end_diag=);
proc freq data=icd10_06_15 ;
%do diag=&start_diag %to &end_diag;
  table diag&diag / out=ao&diag(rename=(diag&diag=diag)) noprint;
data ao_list ;
  set ao&start_diag - so&end_diag ;
  by diag;
proc summary data=ao_list ;
  by diag ;
  output out=ao sum(count)=count;
proc sort data=ao;
  by descending count;

Note that if the same subject has the same diagnosis listed under more than one of the DIAGx columns then that subject will be counted more than once.

To get unique subject counts first transpose the data and then eliminate the duplicates before counting.  You will need a set of BY variables that uniquely identifies the observations in the original dataset.  Let's assume that variable is ID.

proc transpose data=icd10_06_15 out=tall(rename=(col1=diag);
  by id;
  var diag1-diag12 ;

proc sort data=tall nodupkey;
  by id diag;

proc freq data=tall order=freq ;
  tables diag / noprint out=ao ;



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1 like
  • 5 in conversation