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;
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;
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;
Hello,
It needs to produce 102 files for each county with its summary statistics included not just one file. Did I make clear?
Thanks
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?
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.
This is the message I got "Apparent symbolic reference NAME103 not resolved"
There is no symbolic reference in the code I proposed. Please post the code you ran.
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;
The NEWFILE option does this, creates a new report on a specified interval.
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
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;
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.
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.
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.
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.