BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

8 REPLIES 8
jklaverstijn
Rhodochrosite | Level 12

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
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Rhodochrosite | Level 12

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
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Rhodochrosite | Level 12
Oke I see. But I stick to my advice: don't do this. I added the link; have a look.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

thanks  i will take  a look

Shmuel
Garnet | Level 18

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
Pyrite | Level 9 Tal
Pyrite | Level 9

thanks Shmuel your approach works  perfectly

Thank you

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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