Including Data from another Table without using Join

[Blomman007]

Hi!

I'm fairly new to SAS (Proc SQL) and feel a little out of my depth here. I've seen what I'm trying to do be done in other programs, but when I try to adapt it to my script it doesn't work and I can't figure out why. I've tried searching for solutions but without luck, any help would be greatly appreciated. I'm using SAS EG 8.3.

I have two tables with data, I want to count data from a column (Called "Account" ) from Table 1 (called "Renewals") and divide it by another counted data column (called "Closed_Accounts") in Table 2 (called "Closed").

Proc sql;
        Create Table Summary As
           Select
              &Month As Month,
		      Count(T1.Account) As Contracts_w_Renewal,
		      Count(Select Distinct Closed_Accounts From work.closed) As Closed_Contracts,
              Calculated Contracts_w_Renewal/Calculated Closed_Contracts format = percent10.2 AS Renewal_Rate
            
          FROM work.Renewals As T1      

	;Quit; 

This error 22-322: Syntax error, expecting one of... you get the rest 🙂 is genereted on this part:

Count(Select Distinct Closed_Accounts From work.closed) As Closed_Contracts,

and I can't figure out why.

All I really want to do is count the number of "Closed Accounts" from that table and have the output like this:

Month                     No of A from T1              No of A from T2                  %-rate

&month.                 x                                      y                                          x/y

Please let me know if I should clarify anything, any assistance is appreciated 🙂 Thanks

[PaigeMiller]

Why would you specify "without using join"?? I would think the most logical way and easiest way is to use a join. But to do a join, we need some sort of ID on which the two tables can be aligned, and I don't see where you have explained that.

Also, please, from now on, when you get an error in the log, SHOW US the log for that PROC (or DATA step), the whole log for that PROC (or DATA step), all of the log, with nothing chopped out, so we can see the code and the ERRORs, WARNINGs, and NOTEs exactly as it appears in the log.

[Blomman007]

I specified "without using Join" because that's the only way I've used to connect data prior to this and since I don't really have a common ID to join them by. I just wanted to collect a Count from the other table.

Allright, thanks for the note on Log, will do that

[FreelanceReinh · Accepted solution]

Hi @Blomman007 and welcome to the SAS Support Communities!

---

@Blomman007 wrote:

Count(Select Distinct Closed_Accounts From work.closed) As ...

---

Is it the number of distinct values of Closed_Accounts in work.closed that you want to select by the above expression? If so, just change the order of the COUNT function and the SELECT statement as in

(select count(distinct closed_accounts) from work.closed) as ...

The outer parentheses around this subquery are important.

[Blomman007]

Awesome! works like a charm. Thanks 😄