SPARSE option in proc freq. Is it possible to only include all possible values of one variable?

[FreelanceReinh]

---

@Hello_there wrote:

Unfortunately that didn't work.

---

So, you still got duplicates? Or an error in the log (if so, please show the log)?

If i break it apart this code returns back the right number of rows (without the counts).

proc sql;
	create table look as
	select * from 
  		(select distinct trt01an from adsl),
  		(select distinct soc_term, pt_term from adae);
quit;

I was wondering if it was possible to do a right join with the counts to that. But it looks like the below code doesn't work.

proc sql;
create table want as
	select soc_term, pt_term, trt01an, count(distinct a.subject) as count
	from adae a
	natural right join
 		select * from
			(select distinct trt01an from adsl),
  			(select distinct soc_term, pt_term from adae)
	group by soc_term, pt_term, trt01an;
quit;

---

To use the first query (creating dataset look) as a subquery in another query you must put it into parentheses: ... right join (select * from (...), (...)).

Your query has changed from your previous post, which used aebodsys and aedecod. Now that soc_term and pt_term come from both sides of the natural right join, you would need the "calculated" keyword before all three items in the GROUP BY clause or simply use the abbreviation

group by 1,2,3;

as discussed earlier in this thread.

[Hello_there]

Your advice worked! Adding the () around the subquery and the calculated before each group by term worked. Thanks again!

I used soc_term/aebodsys and pc_term/aedecod interchangeably; i should have been more consistent, sorry about the confusion.

Also as for the first question, there were no errors in the log, but it was most likely due to the variable mix up on my part that caused the duplicates.