updating a table

[Tal]

hi,

We have a  table (table_a) on sql server such as

id   1/1/2017  2/1/2017   3/1/2017  4/1/2017

---------------------------------------------------------------

10   123         555          null           null

11   456        666           null          null

 meaning that table has not been updated with this month data yet which i have as a  dataset:(table_b)

id        3/1/2017

--------------------

10      189

11      123

what is the best way to update table_a?

i am thinking of

create table_a as select *from table_a,table_b where a.oid=b.oid;quit;

but that is kinda overwriting a table and  dont know how to do this using "update table_a" as i am not adding rows to table_a but actually adding columns

I am doing this in SAS and the dataset i have has about 200 records

any one please?

thx

[jklaverstijn]

Hi Tal,

As a rule you cannot add columns to a table without rewriting it. What we see here is actually a case of poor data modelling. It is generally way more useful to have a model transposed from what you show:

id date value

10 1/1/2017 123

11 1/1/2017 456

10 2/1/2017 null

etc

Then your updates would simply be additional rows to the table or updates of the value column, which is easy and efficient. Your table design will not have to change with every update and analysis would be so muych easier as well.

Hope this helps,

- Jan.

[Tal]

thanks Jan,

the sql server table has way too many fields actually so not sure how  transposing with work but overwriting it wont hurt  right?

[jklaverstijn]

If it hurts or not is not up to me to decide. It is not efficient as all data will have to be rewritten even if it diodn't change. Dependiong on the number of rows and columns that may be or over time become a problem.

And rewriting is not the only issue I pointed at. Depending on what operations (reporting, analysis) are done on the table, the changing structure may be an issue. It means the program will have to change after every update. Also as some business data are actually stored inside column names instead of column values you will always have to do some stuntwork before making sense of it, subsetting time periods etc.

SAS can help you transpose the table and make your life a lot easier. There is a blog post on this kind of modelling problem. I will link to it when I find it. The general consensus about your current approach is: don't.

UPDATE: Found the post: Common messy data problems and how to tidy them in SAS. Your issue is Messy Data Scenario 1 – Dimension values stored as column names.

Regards,

- Jan.

[Tal]

i am not changing the structure of the  table. All fields exist already I am only updating columns with null values but if  you can find those links would be appeciated.

thx

[jklaverstijn]

Oke I see. But I stick to my advice: don't do this. I added the link; have a look.

[Tal]

thanks  i will take  a look

[Shmuel · Accepted solution]

Am I right: you want to update table_a by trunsactions in data_b ?

If you have tose datsets already in SAs you can do:

data data_a;

update data_a data_b;

by id;

run;

that is, assuming each daily data has its own variable.

You haven't show the variable names. The header 3/1/2017 is not a valid sas name.

[Tal]

thanks Shmuel your approach works  perfectly

Thank you