Help using Base SAS procedures

updating a table

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 444
Accepted Solution

updating a table

[ Edited ]

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  datasetSmiley Sadtable_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


Accepted Solutions
Solution
‎03-09-2017 08:02 PM
Trusted Advisor
Posts: 1,553

Re: updating a table

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.

View solution in original post


All Replies
Super Contributor
Posts: 436

Re: updating a table

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.

Super Contributor
Super Contributor
Posts: 444

Re: updating a table

Posted in reply to jklaverstijn

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?

Super Contributor
Posts: 436

Re: updating a table

[ Edited ]

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.

Super Contributor
Super Contributor
Posts: 444

Re: updating a table

Posted in reply to jklaverstijn

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

Super Contributor
Posts: 436

Re: updating a table

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

Re: updating a table

Posted in reply to jklaverstijn

thanks  i will take  a look

Solution
‎03-09-2017 08:02 PM
Trusted Advisor
Posts: 1,553

Re: updating a table

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.

Super Contributor
Super Contributor
Posts: 444

Re: updating a table

thanks Shmuel your approach works  perfectly

Thank you

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 299 views
  • 3 likes
  • 3 in conversation