Write and run SAS programs in your web browser

Proc SQL question

Reply
Frequent Learner
Posts: 1

Proc SQL question

Hi

 

I am relatively new to the world of SQL and am now using SAS Studio as part of my role, so please forgive me if this is an obvious question.

 

I am trying to write a SQl statement that will tell me how many values in one list don't match those in another list, in this instance Cost Centres, but when i run the report it returns hundreds of thousands of rows rather than an expected 30 or 40. Can anyone signpost me as to where I am going wrong?

 

proc sql;

 

create table DQ As

select case when r.CostCentre ^= d.CostCentredq then 1
else 0
end as WrongCostCentre

from data.resources r, valibla.dq_res d;

 

quit;

 

Thanks in advance for any help

Contributor
Posts: 74

Re: Proc SQL question

You can make this easier by doing a subquery, for example if we create a data set of records from SASHELP.CLASS of those aged under 15 like this:

 

data under_15;
	set sashelp.class(where=(age<15));
run;

We can then get a count of all records in SASHELP.CLASS where age is 15 or over with a subquery:

 

proc sql;
	select count(age)
	from sashelp.class
	where age not in(select age from under_15);
quit;

If you want to create a data set with the over 15's in then do this:

 

proc sql;
	create table over_15
	as select *
	from sashelp.class
	where age not in(select age from under_15);
quit;
	
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: Proc SQL question

To get good answers we need to see what your working with, so post test data in the form of a datastep.

This is just a guess:

proc sql;
  create table WANT as
  select  count(COST_CENTER)
  from    (select distinct COST_CENTER from HAVE1 except select distinct COST_CENTER from HAVE2)
  group by  COST_CENTER;
quit;
  
Regular Learner
Posts: 1

Re: Proc SQL question

[ Edited ]

This may have been answered adequately already but I didn't see any closure... 

 

The most direct answer to your question is that you forgot the where clause after the join.   

 

proc sql;

    create table DQ As

      select  

           case  

              when r.CostCentre ^= d.CostCentredq then 1
              else 0
            end as WrongCostCentre

      from data.resources r,  valibla.dq_res d

      where r.apples=d.apples AND r.oranges=d.oranges;

quit;  

 

If you really just want the rows where WrongCostCentre  =1 then add to the where clause, or add the having clause:

 

proc sql;

    create table DQ As

      select  

           case  

              when r.CostCentre ^= d.CostCentredq then 1
              else 0
            end as WrongCostCentre

      from data.resources r,  valibla.dq_res d

      where r.apples=d.apples AND r.oranges=d.oranges

      having WrongCostCentre=1;  /* Some would consider this an abuse of the having clause */  

quit;  

 

Of course, if you only want WrongCostCentre=1 rows, you don't need the CASE statement in the query. It would just be another condition in the where clause. 

 

Post a Question
Discussion Stats
  • 3 replies
  • 69 views
  • 0 likes
  • 4 in conversation