topic If data present in any of the tables(key varaibles) then condition as yes else no in SAS Programming

If data present in any of the tables(key varaibles) then condition as yes else no

Nithya18 — Tue, 20 Nov 2018 07:23:22 GMT

Hello,

I have asked to check if data present in any of the four tables then populate the condition as Y else N based on the key columns.

How can I achieve it.

Regards,

Nithya

Re: If data present in any of the tables(key varaibles) then condition as yes else no

PeterClemmensen — Tue, 20 Nov 2018 07:31:50 GMT

Hi and welcome to the SAS community 🙂

When you ask a question like this, you have to be more specific. For example: "if data present". What data? And what four tables?

Please be more specific in your requirements. And if you want a full usable code answer, provide example data.

Regards.

Re: If data present in any of the tables(key varaibles) then condition as yes else no

Nithya18 — Tue, 20 Nov 2018 08:06:54 GMT

Thank you!!
For Example,
if data present in any of the cond1,cond2,cond3,cond4 tables and key column is fcl_id,cus_id,app_id. Then need to provide flag as Y else N.
proc sql;
connect to Teradata as db(user=xxx pwd='xxx');
create table cond as
select * from connection to db(
select distinct a.*,b.*,c.*,d.*,
case when fcl_id^=. or cus_id ^=. or app_id^=. then 'Y' else 'N' end as condition
from cond1 as a
left join
cond2 as b
on a.fcl_id=b.fcl_id and a.cust_id=b.cust_id and a.app_id=b.app_id
left join
cond3 as c
on a.fcl_id=c.fcl_id and a.cust_id=c.cust_id and a.app_id=c.app_id
left join
cond4 as d
on a.fcl_id=c.fcl_id and a.cust_id=c.cust_id and a.app_id=c.app_id
);
disconnect from db;
quit;

But it doesn't work. Kindly suggest the best method to accomplish.

Re: If data present in any of the tables(key varaibles) then condition as yes else no

RW9 — Tue, 20 Nov 2018 08:52:18 GMT

Please post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

and what you want to see out at the end. Showing some code and saying it doesn't work tells us nothing. We need to be able to see inputs/outputs to have any idea of what is going on.

On your code, I strongly doubt that is the best solution. You are first off selecting all columns from all four tables, e.g. a.*. That cannot be what you want is it? Second you are using left joins, so only data which appears in the preceeding data will appear in the output. So only data in B which has a match in a on the clause will appear, again I highly doubt this is what you mean.

What I would suggest is that you create datasets of the data you actually want, then do a simple merge:

proc sql;
  connect to teradata as db(...);
  create table a as (select fcl_id,cust_id,app_id,"A" as tab1 from cond1 order by fcl_id,cust_id,app_id);
  create table b as (select fcl_id,cust_id,app_id,"B" as tab2 from cond2 order by fcl_id,cust_id,app_id);
  create table c as (select fcl_id,cust_id,app_id,"C" as tab3 from cond3 order by fcl_id,cust_id,app_id);
  create table d as (select fcl_id,cust_id,app_id,"D" as tab4 from cond4 order by fcl_id,cust_id,app_id);
  disconnect from db;
quit;

data want;
  merge a b c d;
  by fcl_id cust_id app_id;
run;

You will then have a complete list of the merged id variables, and four columns tab1-4 which show presence in each table.