topic Re: assign a missing value in a table A from a specific value from a table B in SAS Programming

assign a missing value in a table A from a specific value from a table B

soumri — Tue, 07 Jun 2016 10:43:05 GMT

Hello everybody;
I ask if there is a method in SAS, which allows to assign a missing value of a variable x in a Table A,

a value y from a table B which contains common values and of course under some conditions, like this for exemple:

Table A:

ID CM NE Rank X

BV012 1 1 1 12.5

BV012 1 1 2 .

BV012 1 1 3 13.0

BV012 11 3 1 .

BV012 11 3 2 13.4

BV012 11 3 3 11.8

BV013 6 2 1 10.9

BV013 6 2 2 09.6

BV013 6 2 3 09.5

BV013 6 2 4 .

BV017 1 1 1 22.1

BV017 1 1 2 17.8

CM=mounth (1,..12)

NE=order activity (1,2 or 3)

Rank=rank (from 1 to 20)

TABLE B: ( common values) given by mounth (CM):

Mounth NE RANK Y

1 1 1 15.5

1 1 2 18.3

1 1 3 17.2

... ... ... ...

1 1 20 03.5

----------------------------------------------

1 2 1 15.8

... ... ... ...

1 2 20 03.4

----------------------------------------------

... ... ... ...

1 3 20 07.3

----------------------------------------------

2 1 1 22.3

... ... ... ...

----------------------------------------------

... ... ... ...

----------------------------------------------

12 3 20 05.5

what i want:

if x from table A = "." then x= y from table B for the same mounth=(1,2,...12), for the same NE=(1,2,3) and for the same Rank = (1,2,... 20).

I did it but it was a very long code. Is there a shorter method with proc sql for exemple.

thanks.

Re: assign a missing value in a table A from a specific value from a table B

RW9 — Tue, 07 Jun 2016 10:58:28 GMT

The way to do this is to merge to the two datasets by the common variables, then use the coalesce function to take the first non missing:

proc sql;
  create table WANT as
  select  A.COMMON_VAR,
             COALESCE(A.VAR,B.VAR) as VAR
  from    FIRST A
  left join  SECOND B
  on       A.COMMON_VAR=B.COMMON_VAR;
quit;

This will take A.VAR if it is not null, otherwise B.VAR.

Re: assign a missing value in a table A from a specific value from a table B

Ksharp — Wed, 08 Jun 2016 03:20:07 GMT

UPDATE . If table is big ,Try Hash Table.

data table_a;
input ID    $          CM      NE       Rank         X;
cards;
BV012       1         1         1              12.5
BV012       1         1         2              .
BV012       1         1         3              13.0
BV012       11       3         1              .
BV012       11       3         2              13.4
BV012       11       3         3              11.8
;
run;

data table_b;
input Mounth    NE           RANK            Y;
cards;
1             1              1                  15.5
1             1              2                  18.3          
1             1              3                  17.2 
;
run;
data want;
 update table_b(rename=(Mounth=CM Y=X)) table_a(in=ina);
 by  CM      NE       Rank ;
 if ina;
run;