topic How to write complex queries in data step in SAS Programming

How to write complex queries in data step

Azeem112 — Tue, 13 Nov 2018 08:01:49 GMT

Dear Community,

I want to write some code from proc sql to data step.

this is my proc sql code.

proc sql;
create table Risk_Register as
select distinct risk_id lable 'Risk ID',
 RISK_NM lable = 'Risk Title',
 RISK_DESC lable 'Risk Description',
 ctrl.control_inst_desc lable 'Control Description', 
 cf1.value_cd as sample1 label 'sample1 ',
 cf2.value_cd as sample2 label 'sample2',
 cf3.value_cd as sample3 label 'sample3',
 u.user_id lable 'User ID',
 u.display_nm lable 'User Name'
from SASOprsk.Risk_l r
left join Sasoprsk.orauser_l u
  on u.user_rk = r.owner_user_rk
left join ( Sasoprsk.ctrl_x_risk_l ctlrsk
  inner join Sasoprsk.Control_inst_l ctrl
   on ctrl.control_inst_rk = ctlrsk.control_inst_rk)
  on ctlrsk.risk_rk = r.risk_rk
left join SASOprsk.orauser_l o
  on r.owner_user_rk=o.user_rk
left join SASOprsk.cust_field_value_l cf1
  on ( r.risk_rk=cf1.business_object_rk and cf1.cust_field_nm = "x_sample_1")
left join SASOprsk.cust_field_value_l cf2
  on ( r.risk_rk=cf2.business_object_rk and cf2.cust_field_nm = "x_sample_2")
left join SASOprsk.cust_field_value_l cf3
  on ( ctrl.control_inst_rk=cf3.business_object_rk and cf3.cust_field_nm = "x_sample_3");
quit;

How can I write this code by using the minimum data step. I can create a new table for each join and then merge all of them using data step but how can we do the same in data step in a single step. Can we write complex queries in data step like above?

Re: How to write complex queries in data step

Kurt_Bremser — Tue, 13 Nov 2018 08:09:48 GMT

No. There's only one by statement allowed in a data step, so you can only combine joins that use the same key variable(s). And you need to prepare your datasets for each merge by doing the necessary sorts.

It is recommended to do such operations in a sequence of steps. You might even find that a sequence of sort and data steps outperforms one single SQL, sometimes by orders of magnitude.

On top of that, you have access to the results of intermediate steps, which makes debugging easier.

Re: How to write complex queries in data step

RW9 — Tue, 13 Nov 2018 08:31:23 GMT

SQL and SAS are different solutions. SQL is built for relational databases - lots of data stored in many small tables which need merging together. SAS is built for datasets, lots of data in one dataset. So use the appropriate tool for the task. As in this case you have a relational database setup, use SQL to link all those datasets, simplest coding method.