how to subset with all available values

[HitmonTran]

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;

[ballardw]

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.

[HitmonTran]

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

[Reeza]

Personally my preference is for that to be part of the reporting process not something you would pre-calculate. I would filter my input data, get my N's, present my table via PROC REPORT and call that macro as many times as necessary. You wouldn't do this ahead of time, but as part of that process....

1. SQL query with filter to get N's
2. PROC REPORT
3. List of sites to call report
4. Execute for all reports.

So IMO you're trying to do something at the wrong place/time making your process more complicated that it needs to be. Its harder to ensure you use the correct values in your process and you end up with a lot of macro variables to debug and manage which takes longer to develop as well. But your work, your preference.

[PaigeMiller]

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?

[HitmonTran]

there's going to be one output with the use of by statement in proc report. so there will be at least 118 pages in the output

[Reeza]

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;

[PaigeMiller]

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.

[HitmonTran]

yeah but the denominator will give me the total counts for all sites instead of each site.

[Reeza]

The solution I provided should do exactly what you asked, even though I don't think it's necessarily the best approach.

[Reeza]

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.

[HitmonTran]

In your example it's using car. So if I have 118 cars to call in the macro, how can i be efficient about this? thanks for your help

[Reeza]

That's what Step #6 does.

[Reeza]

%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.