The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

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

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;

SASfreq example.jpgExcel output example.jpg 


Accepted Solutions
Solution
‎04-12-2018 11:35 AM
Super User
Posts: 23,928

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

Posted in reply to Wolverine

Use PROC TRANSPOSE on the 

temp.results_v&version._Def&defnumb.

And then proc print that result. 

View solution in original post


All Replies
Solution
‎04-12-2018 11:35 AM
Super User
Posts: 23,928

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

Posted in reply to Wolverine

Use PROC TRANSPOSE on the 

temp.results_v&version._Def&defnumb.

And then proc print that result. 

Contributor
Posts: 60

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

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)
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 169 views
  • 0 likes
  • 2 in conversation