topic Re: Creating output file with multiple variables, each in its own column in ODS and Base Reporting

Creating output file with multiple variables, each in its own column

Wolverine — Thu, 05 Apr 2018 16:16:18 GMT

I have several statistics that are reported for 17 case definitions that I'm testing. I'd like to put them in a format that is easier to read and make comparisons. The final goal is to put them together in an Excel file, but I think I can modify syntax I already have to handle that IF I can get SAS to display the output correctly. Here is my current code, which displays the results on several rows instead of on a single row. I'm also attaching pix of the SAS freq output and a shell of what I would like the final table to look like.

ODS TRACE ON;
DATA temp.claim_line_it_DX_def&defnumb._all;
	MERGE temp.claim_line_it_DX_def&defnumb._TP temp.claim_line_it_DX_def&defnumb._FP
		temp.claim_line_it_DX_def&defnumb._FN temp.claim_line_it_DX_def&defnumb._TN
		temp.claim_line_it_DX_def&defnumb._Excl;
	BY ROWID;
	
Sensitivity = TP_count / (TP_count + FN_count);
Specificity = TN_count / (TN_count + FP_count);
Area_under_curve = (Sensitivity + Specificity)/2;

title1 "Case Definition &defnumb. - A count of at least 1 SCA claim (D5700, D5701, D5702)(Version &version.)";
PROC FREQ;
	TABLES TP_count FP_count FN_count TN_count Excl_count Sensitivity Specificity
			Area_under_curve;

ODS OUTPUT OneWayFreqs(match_all)=temp.results_v&version._Def&defnumb.;
PROC FREQ DATA = temp.claim_line_it_DX_def&defnumb._all;
	TABLES TP_count FP_count FN_count TN_count Excl_count Sensitivity Specificity
			Area_under_curve;
RUN;

ODS LISTING;
PROC PRINT DATA = temp.results_v&version._Def&defnumb.;
RUN;

ODS TRACE OFF;

Re: Creating output file with multiple variables, each in its own column

Reeza — Thu, 05 Apr 2018 16:19:36 GMT

Use PROC TRANSPOSE on the

temp.results_v&version._Def&defnumb.

And then proc print that result.

Re: Creating output file with multiple variables, each in its own column

Wolverine — Thu, 12 Apr 2018 15:34:41 GMT

Thanks! Proc Transpose sent me in the right direction, but I had to play with it a bit to get the proper output. Here is the final section of code

%macro defmerge (defnumbm,defname);
%let version=9;

title1 "Def&defnumbm. &defname.";

ODS TRACE ON;
ODS OUTPUT OneWayFreqs(match_all)=temp.results_v&version._Def&defnumbm.;
RUN;

PROC TRANSPOSE DATA = temp.claim_line_it_DX_Def&defnumbm._all out = temp.results_v&version._Def&defnumbm._t;

PROC TRANSPOSE DATA = temp.results_v&version._Def&defnumbm._t out = temp.results_v&version._Def&defnumbm.(DROP=_NAME_);

ODS LISTING;
PROC PRINT DATA = temp.results_v&version._Def&defnumbm. ;
RUN;

ODS TRACE OFF;

%mend;
%defmerge(1, - A count of at least 1 SCA claim)
%defmerge(2, - A count of at least 2 SCA claims)
%defmerge(3, - A count of at least 3 SCA claims)