So this is code I created - and can be used by my entire team.  Table B is not a static table... meaning, it can be a different table (although it has the same columns) but we rename it as Table B so that we can compare it to our master table, Table A.  This is explained in detail below:   For example, on any day, Table B could look like this:   Ref Appname Role Name  Business Friendly Description  SOX Critical (Yes or No) Role Composition: Semicolon-separated list  SoD conflict: Semicolon-separated list Role Owner RACF INHERITANCE: Is the role "Derived"? If Inheritance Is Yes, What does the role Inherit From? (Semicolon separated list) Type Does this Entitlement/Role grant Privileged/Elevated Access (Yes or No) If Change, what changed? 1 OuTrade Admin System Admin/Support Group Yes $shisk   UGPS16 Yes Invnrsch;Slnsast;Cimpnmts Change Yes role composition, owner 2   Clr Clearance and Settlements Group Yes %fjskff   UG6R45 No   Change Yes role composition 3   Clsup Client Support and Onboarding Group Yes %0custlui   UG6R45 No   Change Yes role composition 4   Clspmgr Client Support and Pledging Manager  Yes %lkscustlui UG6R45 No   Change Yes role composition   or on another day Table B could look like this:   Ref Appname Role Name  Business Friendly Description  SOX Critical (Yes or No) Role Composition: Semicolon-separated list  SoD conflict: Semicolon-separated list Role Owner RACF INHERITANCE: Is the role "Derived"? If Inheritance Is Yes, What does the role Inherit From? (Semicolon separated list) Type Does this Entitlement/Role grant Privileged/Elevated Access (Yes or No) If Change, what changed? 1 ALOPS Admin Client Support and Onboarding Group Yes %fjskff   UGPS16 Yes Invnrsch;Slnsast;Cimpnmts Change Yes role composition, owner 2   Clr Clearance and Settlements Group Yes %0custlui   UG6R95 No   Change Yes role composition 3   Admin Client Support and Onboarding Group Yes %0custlui   UG6R45 No   Change Yes role composition 4   Admin System Admin/Support Group Yes %lkscustlui UGPS29 No   Change Yes role composition       Here is Table A: Profile Application Role Profile Description SoxCritical Privileged Owner rolecomposition DESCRIPTION Conflict OuTrade Clspmgr No Description TRUE TRUE UG6R45 seldistrade Client Support and Pledging Manager OuTrade Opsmgr No Description TRUE TRUE UG6R45 uhold Operations Administration Group OuTrade MO No Description TRUE TRUE UG6R45 rqsttrdhist Middle Office Group OuTrade MO No Description TRUE TRUE UG6R45 showlog Middle Office Group ALOPS Connect No Description FALSE FALSE FUS332 uhold Middle Office Group ALOPS Connect No Description FALSE FALSE UL9V42 uhold Middle Office Group ALOPS Connect No Description TRUE TRUE U4GL3F showlog Middle Office Group BXTRAN Slimsfx No Description TRUE TRUE UF442K1 seldistrade Operations Administration Group BXTRAN Slimsfx No Description FALSE TRUE U4GL3F seldistrade Operations Administration Group BXTRAN Slimsfx No Description TRUE FALSE U4GL3F seldistrade Operations Administration Group   So if on Monday my Table B looks like my first sample(above), I want all records from Table A and only those from Table B that match.   If on another day my Table B looks like my second sample above, I want all records from Table A and only those form Table B that match.   I'd like SAS to search for all records in Table A, that equal the variable "Appname" listed in Table B, and left join Table B where there is a match.  How do I do this?  does this example help? ... View more

Thanks @Kurt_Bremser and @SuryaKiran- can you help me take it a step further?   Now that I know this is the error (residing in the where clause), how do I tell SAS to search for the value in b.appname?  See what I mean in the example below...I changed the column names for simplicity.   For example and simplicity, Table A has several obs: name address state cars john 555 jolly ga f150 mike 444 keys fl lexus lou 333 snow la camry harold 222 frills ga civic greg 111 yodle ga pilot susan 777 sleds tx f250 michelle 999 elves sc navigator   Table B has fewer obs: name address state john 555 jolly ga greg 111 yodle ga harold 222 frills ga   If  the value of "state" is dynamic or changes in Table B, how do I get all instances of Table A, where Table B matches?    I only want the values to compare when they match Table A.  This is how I would write the code.    proc sql: create table test as      select a.*,                b.name                b.state from table A left join table B on (a.name = b.name) where a.state = b.state;    But now that I know this is incorrect, how do I get all obs from table A and only those from Table B that match?  I can't simply put where a.state = "ga" because I don't have time to open Table B to see the value of state and enter it into the code.  I want SAS to do it automatically.   So if Table A has 100 obs that matches state "ga" and only 10 obs that matches "ga" in table B, then I want all 100 obs and only those 10 from table B.     If Table A has 150 obs that matches "fl" and only 40 obs that matches "fl" in table B, then I want all 150 from table A and only those from table B that match.     ... View more

Hi Tom, The problem statement is clear.   I have a dataset "have" that is created via Proc Sql join (doesn't matter how many datasets it takes to create it).  I'm just providing example code to show how I created it.   I want one dataset "want", that contains (or keeps) variables based on the condition of the if statement.  If a variable contains "No Match", then I want the dataset to keep only those variables and/or drop the others.      Unfortunately, the "want" in my original code isn't giving me the results I want.   I will try to think of alternatives as well - but thanks to all the members hear and the options provided so far!  Hope we can get this solved today.   ... View more

I was checking this code from @Haikuo     data _null_; set have end=done; array p(*) a1 a2 a3 a4 a5 a6 b1 b2 b3 b4; array _name(10) $30 _temporary_ ; do i=1 to dim(p); if p(i)='No Match' then _name(i)=vname(p(i)); end; if done then call symputx ('del',catx(' ', of _name(*))); run; data final; set have(drop=&del); run;   This works a little, because if a2 = "No Match" then it will drop any other variable in a new dataset.  I could swap drop= for keep= if I wanted to keep certain variables.     However, if b3 = "No Match" then the same variables contained under &del in the previous datastep will be dropped...  I need different variables conditionally to be dropped if a certain variable is selected.   Is there any way to modify the macro above?  It gives me the first "want" I'm looking for - but the macro is unable to drop different variables if a condition is met (in this case, if b3 = "No Match"). ... View more

So I set this up as you stated -  data sod (keep=Role SOD_Conflict conflict sodconflictstatus); data bfd (keep='bfd'n Description bfd_status); set &app.RoleStatusRpt; if sodconflictstatus = "No Match" then output work.sod; if bfd_status = "No Match" then output work.bfd; run;   But received the following error: 26 data bfd (keep='business friendly description'n Description bfd_status); 27 set &app.RoleStatusRpt; 28 if sodconflictstatus = "No Match" then output work.sod; ________ 455 ERROR 455-185: Data set was not specified on the DATA statement. 29 if bfd_status = "No Match" then output work.bfd; 30 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.BFD may be incomplete. When this step was stopped there were 0 observations and 3 variables. WARNING: Data set WORK.BFD was not replaced because this step was stopped. ... View more

Hmmm... I don't think that gives me the desired outcome....because i don't get a new dataset with only the desired columns, if a variable has "No Match".   See this example below I pulled from @Haikuo .  This gives me only one output, but I need multiple outputs based on a condition.   /*this code will give me some of what i want - but not all.*/ data _null_; set have end=done; array p(*) a1 a2 a3 a4 a5 b1-b15; array _name(5) $30 _temporary_ ; do i=1 to dim(p); if p(i)='No Match' then _name(i)=vname(p(i)); end; if done then call symputx ('del',catx(' ', of _name(*))); run; data final; set have(drop=&del); run; If a2 = "No Match" then all other columns are dropped in the new dataset.    However, I want to keep a few columns. For instance, if a2 = "No Match" then I want a new dataset with a1, a2, and a3. If b3 = "No Match" then I want t a new dataset with b3, b4, and b5....or whatever columns I choose based on the condition of a2 or b3. */ Is there a way to do this? Build a new dataset where columns(variables) are kept / dropped based on a condition of a column(variable) in a previous table?? ... View more

THanks!! ... View more

thanks @ballardw, this gives me a start - thanks for taking the time to provide a clear example. ... View more

great. ... View more

I used this code as a test... but it failed.  Pls help.   data test; set work.ACH; check = varnum("work.ach","EmpID"); run;   I thought it would give me the result of check = 1 ; which is the position of EmpID.  What am I missing?   My log: 26:17 NOTE: Invalid numeric data, 'work.ach' , at line 26 column 17. NOTE: Argument 1 to function VARNUM(.,'EmpID') at line 26 column 10 is invalid. ... View more

For example, 2018 file = EmpID, Gender, Pay, Dept., Date   2017 file = EmpID, Date, Gender, Pay, Dept.,    Output would something like:   Error - column 2 or "Date" is not in 2nd position in 2018.   or     Error - column 2 is out of order from 2017 archive. ... View more