The SAS Output Delivery System and reporting techniques

ODS destination creating summary statistics in different files

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

ODS destination creating summary statistics in different files

Hello,

 I want the summary statistics output of individual counties in a rtf fromat. Altogether there should be 102 rtf files in the folder county2016.  I cannot output the summary statistics in the given directory. The macro output_all only creates multiple datasets but couldn't produce the output files. I appreciate any input to solve this problem.

 

PROC SQL noprint ;
SELECT COUNT(DISTINCT COUNTYNAME) INTO : Nobs FROM QSSTOTAL;
SELECT DISTINCT COUNTYNAME INTO : NAME1-:NAME%LEFT(&Nobs) FROM QSSTOTAL;QUIT;
%macro output_all;
%DO i=1 %TO &Nobs;
%put  &&name&i;
%end;
DATA %DO COUNT=1 %TO &Nobs;
%SYSFUNC(COMPRESS (&&NAME&COUNT,/,PS))
%END;
;
SET qsstotal;
SELECT (COUNTYNAME) ;
%DO COUNTY=1 %TO &Nobs;
WHEN ("&&NAME&COUNTY") OUTPUT
%SYSFUNC(COMPRESS (&&NAME&COUNTY,/,PS));
%END;
OTHERWISE;
end;
run;

ods rtf file = "C:\H\COUNTY2016\%SYSFUNC(COMPRESS (&&NAME&county,/,PS)).rtf"
bodytitle;
title;
proc freq data=qsstotal;
tables q1--q10;* q2 q3 q4 q5 q6 q7 q8 q9 q10; where countyname="%SYSFUNC(COMPRESS (&&NAME&COUNTY,/,PS))";
*by countyname;
;
run;

ods rtf close;

%mend;
options mprint;
%output_all;


Accepted Solutions
Solution
‎05-13-2016 01:57 PM
Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

Hello,

Thank you very much for helping me out. Finally , with a small change in your suggestion, I got the output that I wanted. Besides, I need to use %sysfunc in ods statement because some county names have '/' symbol and as a macro resolving values produce an extra '/ ' in the directory , so the an error  came as "Physical file not found" for those counties with symbo '/'.  with%Sysfunc options this will be solved. The code below should perfectly run. Awesome! I'm very happy. Will be in touch. 

 

PROC SQL noprint ;

 SELECT COUNT(DISTINCT COUNTYNAME) INTO : Nobs FROM QSSTOTAL;

 SELECT DISTINCT COUNTYNAME INTO : NAME1-:NAME%LEFT(&Nobs) FROM QSSTOTAL;QUIT;

%let nobs=&sqlobs;

%macro output_all;

%DO i=1 %TO &Nobs;

%put Processing &&name&i;

proc sort data=QSStotal out=class1;

by countyname;

run;

ods rtf file="R:\H\COUNTY2016\%SYSFUNC(COMPRESS (&&&NAME&i,/,PS)).rtf"

newfile=bygroup;

title "County is &&&NAME&i ";

proc freq data=class1;

 tables q1--q10;

 where countyname="&&&NAME&i"; 

run;

 

ods rtf close;

%end;

%mend;

options mprint;

%output_all;

View solution in original post


All Replies
Respected Advisor
Posts: 4,606

Re: ODS destination creating summary statistics in different files

Why not simplify things a lot with NEWFILE= option?

 

ods rtf file = "C:\H\COUNTY2016\County_1.rtf" NEWFILE=BYGROUP bodytitle;
title;

proc freq data=qsstotal; tables q1--q10; by countyname; run; ods rtf close;
PG
Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

Hello,

It needs to produce 102 files for each county with its summary statistics included not just one file. Did I make clear?

 

Thanks

Grand Advisor
Posts: 16,411

Re: ODS destination creating summary statistics in different files

No.

 

Do you need 102 files, one for each county

OR

 

102 files for each county, so say 10 counties, then thats 1020 files. 

 

At any rate the NEWFILE creates a new file for each BY group which seems a lot like what you're doing. Is there a reason you think it won't work?

Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

Hello,

 

Yes, I need 102 files , one for each country. I think NEWFILE option works but my macro is not resolved to individual value. 

The problem should be here:    

 

%SYSFUNC(COMPRESS (&&NAME&count,/,PS)).rtf"

 

and 

 

where countyname="%SYSFUNC(COMPRESS (&&NAME&COUNTY,/,PS))";

 

I appreciate your insight.

 

 

 

 

Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

This is the message I got  "Apparent symbolic reference NAME103 not resolved"

Respected Advisor
Posts: 4,606

Re: ODS destination creating summary statistics in different files

There is no symbolic reference in the code I proposed. Please post the code you ran.

PG
Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

Hello PG,

How are you? I really appreciate your expertise.

The countyname variable has 102 unique counties. I need individual summary for each county . That means, there will be 120 rtf files with individual summary. For example, in countyA rtf file we should have countyA summary statistics, countyB rtf will have countyB summary statistics and so on. My macro variables don't get resolved with the ods statement.

 

 

 Here's my original code:

PROC SQL noprint ;
SELECT COUNT(DISTINCT COUNTYNAME) INTO : Nobs FROM QSSTOTAL;
SELECT DISTINCT COUNTYNAME INTO : NAME1-:NAME%LEFT(&Nobs) FROM QSSTOTAL;QUIT;
%macro output_all;
%DO i=1 %TO &Nobs;
%put Processing &&name&i;
%end;
DATA %DO COUNT=1 %TO &Nobs;
%SYSFUNC(COMPRESS (&&NAME&COUNT,/,PS))
%END;
;
SET qsstotal;
SELECT (COUNTYNAME) ;
%DO COUNTY=1 %TO &Nobs;
WHEN ("&&NAME&COUNTY") OUTPUT
%SYSFUNC(COMPRESS (&&NAME&COUNTY,/,PS));
%END;
OTHERWISE;
end;
run;
ods rtf file = "R:\H\COUNTY2016\%SYSFUNC(COMPRESS (&&NAME&count,/,PS)).rtf"
newfile="&&Name&count" bodytitle;
title;
proc freq data=qsstotal;
tables q1--q10;where countyname="%SYSFUNC(COMPRESS (&&NAME&COUNTY,/,PS))";
by countyname;
run;

ods rtf close;

%mend;
options mprint;
%output_all;

Grand Advisor
Posts: 16,411

Re: ODS destination creating summary statistics in different files

The NEWFILE option does this, creates a new report on a specified interval.  

 

image.jpeg

Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

Hello,

Are you suggesting me to use like this in ods statement?

ods rtf file = "R:H\COUNTY2016\&&&name&i...rtf"  

newfile=

proc freq data=qsstotal;
 tables q1--q10;
 where countyname="&&&NAME&i";
Thanks

 

 

Grand Advisor
Posts: 16,411

Re: ODS destination creating summary statistics in different files

You can run the code....what did it produce?

 

proc sort data=sashelp.class out=class;
by age;
run;

ods rtf file="C:\_localdata\temp\output.rtf" newfile=bygroup;

proc freq data=class;
by age;
table sex;
run;

ods rtf close;
Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

Hello,

It did produce six rtf files. When I utilized this idea in my program, I got exactly what I wanted but the fileneme should go with the county name. instead,It came with county number.In this regard , should we need the macro to name the appropriate file by its county name?

Thoughts?

Thanks.

 

Grand Advisor
Posts: 16,411

Re: ODS destination creating summary statistics in different files

I tried looking at your code, but there's a lot of things to change so here's the solution with NEWFILE instead. I don't know how the naming structure works.

 

ods rtf file = "R:\H\COUNTY2016\County.rtf"
newfile="bygroup" bodytitle;
proc freq data=qsstotal;
by countyname;
tables q1--q10;;

run;

ods rtf close;

If you absolutely want to control the name and the NEWFILE doesn't work for you, then I'd suggest changing your code to the following. 

Rather than do slices of data and create separate output datasets, just filter on the PROC FREQ with a WHERE. 

 

PROC SQL noprint ;

SELECT DISTINCT COUNTYNAME 
INTO : NAME1- 
FROM QSSTOTAL;
QUIT;
%let nobs=&sqlobs;



%macro output_all;
%DO i=1 %TO &Nobs;
%put Processing &&&name&i;

	ods rtf file = "R:\H\COUNTY2016\County &&&name&i..rtf"
		proc freq data=qsstotal;
		tables q1--q10;
		where countyname="&&&name&i";
		run;
	ods rtf close;
%end;
%mend;

options mprint;
%output_all;

Obviously this is all untested since I have no data. 

 

Good Luck.

Occasional Contributor
Posts: 16

Re: ODS destination creating summary statistics in different files

Hello Rezza,

 

Atleast with your second program, I 'm able to produce the output but it's limited to first county only. Do we need to develop a loop for the ods statement? Green light should be waiting.Just a little twist. Thank you in advance. Have a great time.

Grand Advisor
Posts: 16,411

Re: ODS destination creating summary statistics in different files

I was missing a semicolon in my ods rtf statement and a period to allow the name to resolve properly - the log throws a bunch of errors which are straight forward to debug.

The code below works with the SASHELP.CLASS dataset, modify it for your project. 

 

PROC SQL noprint ;

SELECT DISTINCT age 
INTO : NAME1- 
FROM sashelp.class;
QUIT;
%let nobs=&sqlobs;



%macro output_all;
%DO i=1 %TO &Nobs;
%put Processing &&&name&i;

	ods rtf file = "C:\_localdata\temp\County &&&name&i...rtf";
		proc freq data=sashelp.class;
		tables sex;
		where age=&&&name&i;
		run;
	ods rtf close;
%end;
%mend;

options mprint;
%output_all;
Post a Question
Discussion Stats
  • 24 replies
  • 631 views
  • 0 likes
  • 3 in conversation