topic Re: data step - subqueries like in proc sql? in SAS Programming

data step - subqueries like in proc sql?

fuhgidabowit — Wed, 31 Jul 2019 13:25:07 GMT

Hi,

what is the equivalent data-step statement for using subqueries to refer to columns of other list.

My SQL-Code:

proc sql;
create table BOOK_HRCTRY_&yesterday as
select *
from WORK.wbit_book_raw_fx
where BOOK_TXNREF in (select BOOK_TXNREF
from WORK.wbit_book_raw_fx
where BOOK_COUNTERPARTYCOUNTRY in (select HRCTRY_ECB from HRCTRY_ECB_LIST) and
SAS_Date = "&yesterday"d and WBIT_MNTLY=1)
order by BOOK_TXNREF;
quit;

Thanks,

Re: data step - subqueries like in proc sql?

PaigeMiller — Wed, 31 Jul 2019 13:29:27 GMT

When you do a MERGE in a data step, you can use the IN= option on any of the data sets being merged, and then only include records for which the proper in variable has a value of 1.

Example:

data abc;
    merge dataset1 dataset2 dataset3(in=in3);
    by id;
    if in3;
run;

Re: data step - subqueries like in proc sql?

Kurt_Bremser — Wed, 31 Jul 2019 13:41:15 GMT

With data steps, you need to solve this in steps:

proc sort
  data=HRCTRY_ECB_LIST (keep=HRCTRY_ECB)
  out=int1
;
by HRCTRY_ECB;
run;

proc sort
  data=wbit_book_raw_fx (
    keep=BOOK_COUNTERPARTYCOUNTRY BOOK_TXNREF SAS_Date WBIT_MNTLY
    rename=(BOOK_COUNTERPARTYCOUNTRY=HRCTRY_ECB)
    where=(SAS_Date = "&yesterday"d and WBIT_MNTLY=1)
  )
  out=int2 (drop=SAS_Date WBIT_MNTLY)
;
by HRCTRY_ECB;
run;

data int3;
merge
  int1 (in=i1)
  int2 (in=i2)
;
by HRCTRY_ECB;
if i1 and i2;
run;

proc sort data=int3 nodupkey;
by BOOK_TXNREF;
run;

proc sort data=wbit_book_raw_fx;
by BOOK_TXNREF;
run;

data BOOK_HRCTRY_&yesterday;
merge
  wbit_book_raw_fx (in=w1)
  int3 (in=i3 keep=BOOK_TXNREF)
;
by BOOK_TXNREF;
if w1 and i3;
run;

Although it looks like a lot of code, note that it took me less than three minutes to write it. Being used to write data step code does this for you. And most of the time was spent deciphering the SQL logic.

Since I don't have data to test against, the code is untested.

With memory-based means (hash objects), one could reduce the number of steps, but you would get code that is less easy to read and understand.