topic Re: advance Sas programming in SAS Programming

advance Sas programming

nid197 — Wed, 27 Feb 2019 19:42:37 GMT

Hi,
Please helo me to solve this

I have table1 where i have created multiole flag columns which are empty.
I have another table2 which will check for the matching of account no and will output the rows which are not matching(matching rows it will remove).
So based on output from table 1 i have to update flag columns in table1.
I need the output table as-

Accno flag1 flag2 flag3 flag4 flag5.......flag20
245789 y n n y y ........ n
678955 n y y y n ......... y
654285 n y y n y .........n

Here account no is the total list of account no and flag1 to flag20 are the tables i am checking where i will find that account no..so basically i will update y-yes and n-no if there.

Please help

Thanks

Re: advance Sas programming

PeterClemmensen — Wed, 27 Feb 2019 19:45:31 GMT

Can you provide some example data of table1?

Re: advance Sas programming

nid197 — Wed, 27 Feb 2019 19:51:12 GMT

No i cant do that..there are some security constraints.but there are some some more variables as new acc no, old acc no,acc no,flag1...flag20
So basically we are checking if old acc no and new acc no are same if not is it updated..based on few more variables.
Thanks

Re: advance Sas programming

Reeza — Wed, 27 Feb 2019 20:41:25 GMT

Make fake data, please. It needs to reflect your data structure and complexity but does not need to be your actual data.

Re: advance Sas programming

nid197 — Wed, 27 Feb 2019 20:56:16 GMT

Well,It looks bit this way

Table1:

Custn acc_no oldtr_acc_no newtr_acc_no flag1-14
112 56786 ab54318tvc bs26514ihs
612 95305 id54318tvc bs97424ird
169 85319 pl75290hds bs6514ihs
932 84326 kd64290ygd bs25325pld
172 75032 la62975jfc bs87424itd

Table2

acc_no oldtr_acc_no newtr_acc_no
56786 ab54318tvc bs26514ihs
95305 tg76428fds bs97424ird
85319 pl75290hds bs6514ihs

I want to update table1 flag column based on the output of table2 such as if table2 has 0 rows then all the acc no are updated because table 2 only has the acc no which are not yet updated.so based on table2 data i want to mark the acc no in table1 flag as updated else not updated

Re: advance Sas programming

ScottBass — Thu, 28 Feb 2019 04:32:33 GMT

And now...paste in a self-contained SAS datastep using datalines for your data, so we don't have to do the work that you should provide.

Paste examples of your source datasets (traditionally called "have", or in your case "have1", "have2") and your target dataset (traditionally called "want"). Although we should be able to figure source(s) and target(s) from the context, so the above dataset names, while helpful, are optional.

Please also paste your code inside a SAS block - click the "running man" icon in the editor to do so.

Re: advance Sas programming

Satish_Parida — Thu, 28 Feb 2019 04:52:06 GMT

This looks like a work of a database trigger.

1. So what do you do when a particular record is removed from table 2?

2. You still have not mentioned about the conditions for flag variables, are all 14 variables dependent on one record in table 2?

Please post data with data lines, so that we don't have to construct the data.

And show us with example how the output should look like for your input.

Re: advance Sas programming

nid197 — Thu, 28 Feb 2019 09:21:46 GMT

The code i wrote is this

Proc sql,
Create table want1 as select b.&colnm.,b.&lkp1.b.&lkp2.,'Accept' as flag_&seq_no from dbn.&tblnm. b Inner join wrk.tbl2 a
On b.&colnm.=a.&lkp1.

---result from these dataset means the records are updated

Proc sql,
Create table want2 as select b.&colnm.,b.&lkp1.b.&lkp2.,'Reject' as flag_&seq_no from dbn.&tblnm. b Inner join wrk.tbl2 a
On b.&colnm.=a.&lkp2.

----------result from these dataset means the records are not yet updated

And again I have 3rd table which i will call as final table where I have created flags 1 to flag 20 wherein if
Want1 is 0 then flag_&seqno is updated with 'accept' and if want2 have 0 records my flag_&seqno is updated to 'Reject'..so basically i am checking conditions from 2 table and updating the third one
Flag_&seqno is the macro variable created for table name.

I hope i am able to explain it not..
PS_cant post the screenshot of sample data due to security constraints