BookmarkSubscribeRSS Feed
Nithya18
Calcite | Level 5

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

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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.

Nithya18
Calcite | Level 5
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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-dat...

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.  

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 565 views
  • 0 likes
  • 3 in conversation