topic Re: Combining two proc sql with count in SAS Programming

Combining two proc sql with count

Solly7 — Thu, 16 Sep 2021 08:10:49 GMT

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;

Re: Combining two proc sql with count

Kurt_Bremser — Thu, 16 Sep 2021 08:29:16 GMT

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

Re: Combining two proc sql with count

Solly7 — Thu, 16 Sep 2021 08:32:33 GMT

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

Re: Combining two proc sql with count

Kurt_Bremser — Thu, 16 Sep 2021 08:33:17 GMT

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