topic Re: updating a table in SAS Procedures

updating a table

Tal — Wed, 08 Mar 2017 16:04:43 GMT

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

Re: updating a table

jklaverstijn — Wed, 08 Mar 2017 16:03:37 GMT

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.

Re: updating a table

Tal — Wed, 08 Mar 2017 16:07:31 GMT

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?

Re: updating a table

jklaverstijn — Wed, 08 Mar 2017 16:27:30 GMT

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.

Re: updating a table

Tal — Wed, 08 Mar 2017 16:26:23 GMT

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

Re: updating a table

jklaverstijn — Wed, 08 Mar 2017 16:30:25 GMT

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

Re: updating a table

Tal — Wed, 08 Mar 2017 16:57:34 GMT

thanks i will take a look

Re: updating a table

Shmuel — Wed, 08 Mar 2017 20:21:02 GMT

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.

Re: updating a table

Tal — Fri, 10 Mar 2017 01:02:05 GMT

thanks Shmuel your approach works perfectly

Thank you