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

I know how to do a simple update query to update if I have the data and am just entering. But not sure how to construct if I am updating fields from another table.

PROC SQL;

UPDATE ReAdm.TINCHI

SET MKT_NM = (Readm.Market_mkt_nm) where (readm.tinchi_mkt_nm) is not null;

RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

data tinchi;

do taxid=1 to 10;

  mkt_nm=rand('uniform');

  if rand('bernoulli',.5) then call missing(mkt_nm);

  output;

end;

proc print data=tinchi; run;

data market;

do taxid=1 to 10;

  market_mkt_nm=rand('uniform');

  output;

end;

run;


proc print data=market; run;

proc sql;

update tinchi a

set mkt_nm = (select market_mkt_nm from market b where a.taxid=b.taxid) where a.mkt_nm is null;

run;

proc print data=tinchi; run;



Obs    taxid     mkt_nm

  1       1      .  

  2       2      .  

  3       3     0.08550

  4       4     0.11229

  5       5     0.59399

  6       6      .  

  7       7      .  

  8       8     0.79186

  9       9      .  

10      10      .  

                market_

Obs    taxid     mkt_nm

  1       1     0.87792

  2       2     0.59555

  3       3     0.31891

  4       4     0.35050

  5       5     0.05793

  6       6     0.61312

  7       7     0.41105

  8       8     0.74894

  9       9     0.77759

10      10     0.17934

Obs    taxid     mkt_nm

  1       1     0.87792

  2       2     0.59555

  3       3     0.08550

  4       4     0.11229

  5       5     0.59399

  6       6     0.61312

  7       7     0.41105

  8       8     0.79186

  9       9     0.77759

10      10     0.17934

View solution in original post

6 REPLIES 6
FriedEgg
SAS Employee

You are confusing table alias and library names unless you are expecting this update to use data from multiple tables.  Your code is insufficient to discern your intentions so help is difficult to provide.

I will assume you are updating a single table with data only in the same table:

/* generate some fake data */

data tinchi;

mkt_nm=.;

do market_mkt_nm=1 to 10;

  if rand('BERNOULLI',.5) then tinchi_mkt_nm=rand('UNIFORM');

  else call missing(tinchi_mkt_nm);

  output;

end;

run;

proc print data=tinchi; run;

/* update mkt_nm to equal market_mkt_nm when tinchi_mkt_nm is missing */

proc sql;

update tinchi

set mkt_nm = market_mkt_nm where tinchi_mkt_nm is not null;

quit;

proc print data=tinchi; run;

      
obsmkt_nmmarket_mkt_nmtinchi_mkt_nm
1.1.
2.2.
3.3.
4.40.48095
5.50.24102
6.60.70152
7.70.61888
8.80.68982
9.9.
10.100.03868


      
obsmkt_nmmarket_mkt_nmtinchi_mkt_nm
1.1.
2.2.
3.3.
4440.48095
5550.24102
6660.70152
7770.61888
8880.68982
9.9.
1010100.03868

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

No. I am updating data in a table with data from another table.

FriedEgg
SAS Employee

It would be best for you to provide some context then in the way of some example data for each of the tables in question.

Provide an example for data of each input table and the expected output.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

All are in the readm library

Tables: Market and TINCHI

Columns: Market: taxid, mkt_nm

TINCHI:taxid, mkt_nm

The TINCHI table has null mkt_nm values, 1562 of them. The market table has the taxid's contained in the market table and TINCHI table. The market table is the master. I want to run an update query so that the 1562 null mkt_nm is taken from the master Market table. Each table has a taxid as the key. I might just have to do a regular proc sql without update. No that will not work. Not sure. Know how to do update query in Access and can view the SQL but not sure how to translate that to SAS

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

One table in the readm library is called tinchi and the other table in the readm library is called market

I need all mkt_nm that is in tinchi and null to be updated to the mkt_nm that is in the market table. My key happens to be a TAXID


FriedEgg
SAS Employee

data tinchi;

do taxid=1 to 10;

  mkt_nm=rand('uniform');

  if rand('bernoulli',.5) then call missing(mkt_nm);

  output;

end;

proc print data=tinchi; run;

data market;

do taxid=1 to 10;

  market_mkt_nm=rand('uniform');

  output;

end;

run;


proc print data=market; run;

proc sql;

update tinchi a

set mkt_nm = (select market_mkt_nm from market b where a.taxid=b.taxid) where a.mkt_nm is null;

run;

proc print data=tinchi; run;



Obs    taxid     mkt_nm

  1       1      .  

  2       2      .  

  3       3     0.08550

  4       4     0.11229

  5       5     0.59399

  6       6      .  

  7       7      .  

  8       8     0.79186

  9       9      .  

10      10      .  

                market_

Obs    taxid     mkt_nm

  1       1     0.87792

  2       2     0.59555

  3       3     0.31891

  4       4     0.35050

  5       5     0.05793

  6       6     0.61312

  7       7     0.41105

  8       8     0.74894

  9       9     0.77759

10      10     0.17934

Obs    taxid     mkt_nm

  1       1     0.87792

  2       2     0.59555

  3       3     0.08550

  4       4     0.11229

  5       5     0.59399

  6       6     0.61312

  7       7     0.41105

  8       8     0.79186

  9       9     0.77759

10      10     0.17934

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 900 views
  • 0 likes
  • 2 in conversation