topic Re: How to replace rows from different table in SAS Programming

How to replace rows from different table

shlomiohana — Mon, 13 Jul 2020 19:08:25 GMT

Hi, everyone.

I need help to replace rows in an existing table.

dataset A:

Branch	target_1	target_2	target_3
1	10	80	10
2	12	50	38
3	30	30	40
4	10	10	80
5	31	29	40
6	44	26	30
7	70	13	17
8	20	40	40

dataset B:

Branch	target_1	target_2	target_3
1	22	22	66
4	44	12	44
6	30	40	30

I want dataset A look like:

Branch	target_1	target_2	target_3
1	22	22	66
2	12	50	38
3	30	30	40
4	44	12	44
5	31	29	40
6	30	40	30
7	70	13	17
8	20	40	40

Any suggestions?

Thanks.

Re: How to replace rows from different table

Shmuel — Mon, 13 Jul 2020 19:14:52 GMT

data want;

update datasetA

datasetB;

by branch;

run;

Re: How to replace rows from different table

mklangley — Mon, 13 Jul 2020 19:21:08 GMT

proc sql;
    create table want as
    select a.branch
          ,case when b.branch is not null then b.target1 else a.target1 end as target1
          ,case when b.branch is not null then b.target2 else a.target2 end as target2
          ,case when b.branch is not null then b.target3 else a.target3 end as target3
    from a left join b on a.branch = b.branch
    ;
quit;

Re: How to replace rows from different table

shlomiohana — Mon, 13 Jul 2020 19:33:18 GMT

Thank you very much, it is possible to get a solution including a loop?

Re: How to replace rows from different table

mklangley — Mon, 13 Jul 2020 20:39:24 GMT

Do you mean a loop through the target1, target2, target3 variables?

%macro loop;
    proc sql;
        create table want as
        select a.branch
            %do i = 1 %to 3;
              ,case when b.branch is not null then b.target&i else a.target&i end as target&i
            %end;
        from a left join b on a.branch = b.branch
        ;
    quit;
%mend;
%loop;

Or are you envisioning some other sort of loop?