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;
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
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;
obs | mkt_nm | market_mkt_nm | tinchi_mkt_nm |
1 | . | 1 | . |
---|---|---|---|
2 | . | 2 | . |
3 | . | 3 | . |
4 | . | 4 | 0.48095 |
5 | . | 5 | 0.24102 |
6 | . | 6 | 0.70152 |
7 | . | 7 | 0.61888 |
8 | . | 8 | 0.68982 |
9 | . | 9 | . |
10 | . | 10 | 0.03868 |
obs | mkt_nm | market_mkt_nm | tinchi_mkt_nm |
1 | . | 1 | . |
---|---|---|---|
2 | . | 2 | . |
3 | . | 3 | . |
4 | 4 | 4 | 0.48095 |
5 | 5 | 5 | 0.24102 |
6 | 6 | 6 | 0.70152 |
7 | 7 | 7 | 0.61888 |
8 | 8 | 8 | 0.68982 |
9 | . | 9 | . |
10 | 10 | 10 | 0.03868 |
No. I am updating data in a table with data from another table.
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.
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
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: