topic Re: ODS destination creating summary statistics in different files in ODS and Base Reporting

ODS destination creating summary statistics in different files

kllamitarey — Wed, 11 May 2016 20:57:38 GMT

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.

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;

Re: ODS destination creating summary statistics in different files

PGStats — Wed, 11 May 2016 21:27:30 GMT

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;

Re: ODS destination creating summary statistics in different files

kllamitarey — Wed, 11 May 2016 21:52:33 GMT

Hello,

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

Thanks

Re: ODS destination creating summary statistics in different files

kllamitarey — Wed, 11 May 2016 21:57:45 GMT

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

Re: ODS destination creating summary statistics in different files

PGStats — Wed, 11 May 2016 22:10:25 GMT

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

Re: ODS destination creating summary statistics in different files

Reeza — Wed, 11 May 2016 22:13:39 GMT

No.

Do you need 102 files, one for each county

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?

Re: ODS destination creating summary statistics in different files

kllamitarey — Thu, 12 May 2016 13:14:25 GMT

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.

Re: ODS destination creating summary statistics in different files

kllamitarey — Thu, 12 May 2016 13:24:27 GMT

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;

Re: ODS destination creating summary statistics in different files

Reeza — Thu, 12 May 2016 14:03:37 GMT

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

Re: ODS destination creating summary statistics in different files

Reeza — Thu, 12 May 2016 17:53:23 GMT

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.

Re: ODS destination creating summary statistics in different files

kllamitarey — Thu, 12 May 2016 22:12:48 GMT

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.

Re: ODS destination creating summary statistics in different files

kllamitarey — Thu, 12 May 2016 22:17:02 GMT

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

Re: ODS destination creating summary statistics in different files

Reeza — Thu, 12 May 2016 22:25:46 GMT

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;

Re: ODS destination creating summary statistics in different files

Reeza — Thu, 12 May 2016 22:39:28 GMT

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;

Re: ODS destination creating summary statistics in different files

kllamitarey — Fri, 13 May 2016 14:24:34 GMT

Hi Reeza,

The only remaining concern is that the program is able to output for only one county(but gave the result exaclty what I wanted). The program ignored remaining 101 counties. The macro resolution was perfect. Why is the do loop processing in the ods statement is limited to only one county(first county)?There is no warning/error in the log as well.

Thanks for helping me.

Have a good day

Re: ODS destination creating summary statistics in different files

kllamitarey — Fri, 13 May 2016 14:46:13 GMT

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.

Re: ODS destination creating summary statistics in different files

Reeza — Fri, 13 May 2016 15:07:01 GMT

Both examples worked perfectly in my case - with names.

Post your code and check it over that it aligns with the sample code.

I only get multiple files. Are there errors in your log?

The NEWFILE method will not allow you to control the name, you'll need to use the macro version if you want county name.

Re: ODS destination creating summary statistics in different files

kllamitarey — Fri, 13 May 2016 15:30:00 GMT

No errors in the log. That's what I got. Attached is the partial output. I wanted CountyA, CountyB, CountyC etc. Inside each file, I got exactly what I wanted. Thanks

Re: ODS destination creating summary statistics in different files

Reeza — Fri, 13 May 2016 15:39:23 GMT

POST YOUR CODE AND LOG.

There are twp solutions provided, which did you use? I can't help debug something I can't see.

Re: ODS destination creating summary statistics in different files

kllamitarey — Fri, 13 May 2016 16:03:55 GMT

I got the result from the program without using the macro. With the macro program, I don't get solution.