topic Re: Assigning value depending on varies filters from a different sources in SAS Programming

Assigning value depending on varies filters from a different sources

amyk — Wed, 27 Feb 2019 20:42:53 GMT

Hi All,

I hope you can help me. I am trying to create a database depending on varies criteria and I am trying to find a way to pull it all together.

I have, say database1 with values and depending on if a variable received from another source (File1) exists then use values from databases2 that have the variable if exists from File1 . I have identified if the variable exists and now I am trying to set that value from database2 to replace value from database1.

Re: Assigning value depending on varies filters from a different sources

Reeza — Wed, 27 Feb 2019 20:53:32 GMT

If you provide some sample data, fake is totally fine, that illustrates your problem with data you're more likley to get a solution.

There's more than likely a way to do this, but a vague general question = a vague general response. If you're just looking for ideas/directions, UPDATE from SQL or data step may be what you need, or you may need to do a join/merge.

Re: Assigning value depending on varies filters from a different sources

amyk — Wed, 27 Feb 2019 21:04:44 GMT

That is the thing I don't know where to start to join things together.

I will try to mock up something that maybe helps better to explain what I am trying to do.

say in database1 if have V_W = "Hello" and if File1 has Var1 = "PPO"

then in database2 where Var1=PPO update database1 with value in database2.

I am struggling with how would I do that.

Re: Assigning value depending on varies filters from a different sources

Reeza — Wed, 27 Feb 2019 21:58:53 GMT

Don't worry about the code or technicalities at this point, create examples of what you have to start with and what you want. Think of as many variations and edge cases as possible.

Honestly, if you don't know that ahead of time it's 10x harder and longer to get the code right.

Re: Assigning value depending on varies filters from a different sources

amyk — Wed, 27 Feb 2019 22:23:19 GMT

Create Table OptionSet

select a, b, c

if a.V_W = "" or C.V_AWZ = "Y" then a.V_W = b.V_W end;

if a.V_X = "" or C.V_BWZ = "Y" then a.V_X = b.V_X end;

if a.V_Y = "" or C.V_CWZ = "Y" then a.V_Y = b.V_Y end;

if a.V_Z = "" or C.V_DWZ = "Y" then a.V_Z = b.V_Z end;

quit.

Re: Assigning value depending on varies filters from a different sources

Patrick — Thu, 28 Feb 2019 01:56:26 GMT

@amyk

I believe it's may be best to start with data. Could you please provide sample data which showcase what you have and then tell us how the desired result should look like.

About terminology: I'm a bit confused what you call "database" and what you call "file". Is database a table in a database and file a SAS table? Or is it something else?

Re: Assigning value depending on varies filters from a different sources

ballardw — Thu, 28 Feb 2019 17:15:05 GMT

@amyk wrote:

Create Table OptionSet

as

select a, b, c

if a.V_W = "" or C.V_AWZ = "Y" then a.V_W = b.V_W end;

if a.V_X = "" or C.V_BWZ = "Y" then a.V_X = b.V_X end;

if a.V_Y = "" or C.V_CWZ = "Y" then a.V_Y = b.V_Y end;

if a.V_Z = "" or C.V_DWZ = "Y" then a.V_Z = b.V_Z end;

quit.

Really recommend following @Reeza's suggestion of data examples. Your pseudocode above references values of multiple variable from multiple tables, A and C, without any consideration of how A and C should relate to ensure the correct values are considered.

Also multiple basically identical comparison/results often is an indication that maybe the data could be better structured