Help using Base SAS procedures

SAS Update Problem

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 238
Accepted Solution

SAS Update Problem

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;


Accepted Solutions
Solution
‎06-13-2012 04:02 PM
Trusted Advisor
Posts: 1,297

Re: SAS Update Problem

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


All Replies
Trusted Advisor
Posts: 1,297

Re: SAS Update Problem

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

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Update Problem

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

Trusted Advisor
Posts: 1,297

Re: SAS Update Problem

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.

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Update Problem

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

Regular Contributor
Regular Contributor
Posts: 238

Re: SAS Update Problem

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


Solution
‎06-13-2012 04:02 PM
Trusted Advisor
Posts: 1,297

Re: SAS Update Problem

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

☑ This topic is SOLVED.

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

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