Hi,
I need to create an output by "siteid". There are 118 siteid's that need to be subset, so it wouldn't make sense for me to individually type it all out. Is there a way where I can automate this process?
data=final;
set x;
where siteid='site1';
run;
*Get denominator , Big N;
proc sql;
select (strip(put(count(distinct usubjid), best.))) into :trt1 from final where siteid;
select (strip(put(count(distinct usubjid), best.))) into :trt2 from final where siteid;
select (strip(put(count(distinct usubjid), best.))) into :trt3 from final where siteid;
quit;
%put &trt1 &trt2 &trt3;
Absolutely no clue what you think that SQL is accomplishing.
And what kind of "an output" is needed?
Are there actually more than 118 site ids in the data?
Do you have a list or data set with the ones you want? The values have to exist somewhere to use for any "automated" process.
If you already have a list then one approach might be
where siteid in ('site1' 'site2' ....);
If you have a data set with the ones you want then perhaps a left join on that data set.
there could be more than 118 siteid's if they add more sites in the future.
I need to create one output .rtf and pdf using proc report by statement. so there will be at least 118 pages. The proc sql is to get the N counts per treatment group
Why do you need an output by SiteID, giving a total of 118 outputs? Wouldn't one output containing all SiteIDs be easier to work with? What is the next step after you do this? What is the end goal here?
That doesn't actually tell us anything about how you plan to use this or why it's needed.
Regardless, here's a straightforward way to create the counts.
proc sql noprint;
create table unique_counts as
select trt, count(distinct usubjid) as count
from final
group by siteID
order by siteID;
quit;
data _null_;
set unique_counts;
call symputx(siteID, count);
run;
%put &site1.;
%put &site2.;
Another method, but I'm not sure how this will assign which values to which macro variables. Test it thoroughly.
proc sql noprint;
select count(distinct usubjid) into :trt1- from final
group by siteID
order by siteID;
quit;
%put &trt1;
%put &trt118;
PROC REPORT can separate outputs into different pages whenever a BY variable changes. No need for you to write your own program to split things up 118 different ways.
Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md
If you have full code that works for one site and then use the process above to generalize it.
%macro myReports(site=);
data _final;
set x;
where siteid="&site";
run;
*Get denominator , Big N;
proc sql noprint;
select (strip(put(count(distinct usubjid), best.))) into :trt1 from final where siteid;
select (strip(put(count(distinct usubjid), best.))) into :trt2 from final where siteid;
select (strip(put(count(distinct usubjid), best.))) into :trt3 from final where siteid;
quit;
proc report data=final nowd;
column ........;
define .....;
run;
proc datasets lib=work nodetails nolist;
delete _final;
run;quit;
%mend;
*get list of all makes;
proc sql;
create table list_sites as
select distinct siteID
from x;
quit;
data run_reports;
set list_sites (obs=2);
str = catt('%myReports(site=', siteID, ');');
call execute(str);
run;
Fill in your PROC REPORT and voila.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.