BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kllamitarey
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
kllamitarey
Obsidian | Level 7

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

24 REPLIES 24
PGStats
Opal | Level 21

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
kllamitarey
Obsidian | Level 7

Hello,

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

 

Thanks

Reeza
Super User

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?

kllamitarey
Obsidian | Level 7

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.

 

 

 

 

kllamitarey
Obsidian | Level 7

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

PGStats
Opal | Level 21

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

PG
kllamitarey
Obsidian | Level 7

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;

Reeza
Super User

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

 

image.jpeg

kllamitarey
Obsidian | Level 7

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

 

 

Reeza
Super User

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;
kllamitarey
Obsidian | Level 7

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.

 

Reeza
Super User

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.

kllamitarey
Obsidian | Level 7

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.

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 24 replies
  • 1977 views
  • 0 likes
  • 3 in conversation