topic SAS Update Problem in SAS Procedures

SAS Update Problem

tmm — Wed, 13 Jun 2012 18:32:44 GMT

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;

Re: SAS Update Problem

FriedEgg — Wed, 13 Jun 2012 19:43:36 GMT

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

Re: SAS Update Problem

tmm — Wed, 13 Jun 2012 19:51:24 GMT

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

Re: SAS Update Problem

tmm — Wed, 13 Jun 2012 19:53:08 GMT

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

Re: SAS Update Problem

FriedEgg — Wed, 13 Jun 2012 19:53:35 GMT

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.

Re: SAS Update Problem

tmm — Wed, 13 Jun 2012 20:02:18 GMT

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

Re: SAS Update Problem

FriedEgg — Wed, 13 Jun 2012 20:02:46 GMT

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