Combining two proc sql with count

[Solly7]

Hi I need help in combining the below two proc sql statements into 1

data HAVE;
input user_type : $20. Saved policy_no username : $20.;
datalines;
R 1 1 ALUPIYA
R 1 1 ALUPIYA
R 0 2 ALUPIYA
R 1 3 ALUPIYA
I 0 5 DLOUIS
I 1 4 DLOUIS
I 1 4 DLOUIS
O 1 6 DMORAKE
O 1 7 DMORAKE
O 1 8 DMORAKE
O 0 9 DMORAKE

;


data WANT;
input user_type : $20. Total_Saved Total_policies username : $20.;
datalines;
R 2 3 ALUPIYA
I 1 2 DLOUIS
O 3 4 DMORAKE
;

proc sql; 
create table Saves as 
select  user_type,
        username,
        count(distinct policy_no) as total_saved
        from HAVE
		where saved = 1
		group by 1,2
		order by 2
;quit;

proc sql; create table policies_total as
  select user_type,
         username,
         count(distinct policy_no) as Total_Policies
    from HAVE
group by 1,2
order by 2;
quit;

[Kurt_Bremser]

Since you create two separate datasets, you cannot combine this; you can only create one dataset in a single CREATE TABLE statement.

[Solly7]

Hi Kurt, I have only one dataset which is HAVE..all I need is to have total_saved, total_policies,username, user_type in one dataset

[Kurt_Bremser · Accepted solution]

Use the solution I gave you here. Just add user_type to the BY statement.