Proc freq with multiple variables

[CathyVI]

Hi,

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;
run;

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;
        run;
    %end;
%mend;
%around(start_diag=1,end_diag=12)
;
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.

[Kurt_Bremser]

Why do you have 12 diagnosis variables, instead of one diagnosis variable with multiple observations per subject?

[CathyVI]

@Kurt_Bremser  this is how the data is set up

[Kurt_Bremser]

Then I suggest you transpose to a long layout first before continuing with your analysis.

[Quentin]

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;
run;

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;
/*... */
run;

data a0;
  set a01-a012;
run;

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.

[Reeza]

Try this instead:

• ods select none;
  ods output onewayfreqs=temp;
  proc freq data=icd10_06_15;
  table diag1--diag12;
  run;
  ods select all;
  
  *Format output;
  data temp2;
  length variable $32. variable_value $50.;
  set temp;
  Variable=scan(table, 2);
  
  Variable_Value=strip(trim(vvaluex(variable)));
  
  keep variable variable_value frequency percent cum:;
  label variable='Variable' 
      variable_value='Variable Value';
  run;
  
  *Display;
  proc print data=temp2(obs=20) label;
  run;
  
  proc freq data=temp2;
  table value / out=ao;
  weight frequency;
  run;

• 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. 

[Tom]

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;
%end;
run;
data ao_list ;
  set ao&start_diag - so&end_diag ;
  by diag;
run;
proc summary data=ao_list ;
  by diag ;
  output out=ao sum(count)=count;
run;
proc sort data=ao;
  by descending count;
run;
%mend;

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 ;
run;

proc sort data=tall nodupkey;
  by id diag;
run;

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