topic Re: merge 1 row table to another column table in SAS Procedures

merge 1 row table to another column table

podarum — Fri, 20 Apr 2012 18:11:04 GMT

Would this be possible somehow? I have a reference table with 1 row. I need to use the data from that table to fill in missing holes from another table. Here's what I mean:

Have:

TABLE1

VarA VarB VarC

34 45 13

TABLE2

City VarAA VarBB VarCC

Tor 64 34 12

Ott . 35 .

Mon 47 . 9

I need: the Holes in TABLE2 to be filled in with Data from TABLE1

WANT:

City N_VarA N_VarB N_VarC

Tor 64 34 12

Ott 34 35 13

Mon 47 45 9

I tried... something like:

data WANT;

merge Table1 Table2;

if VarAA = . then N_VarA = VarA; else N_VarA = VarAA;

run;

endrsubmit;

Thanks..

Re: merge 1 row table to another column table

Haikuo — Fri, 20 Apr 2012 18:22:47 GMT

Here is one option, use array if you have too many variables:

data TABLE1;

input VarA VarB VarC;

cards;

34 45 13

;

data TABLE2;

input City$ VarAA VarBB VarCC;

cards;

Tor 64 34 12

Ott . 35 .

Mon 47 . 9

;

data want (drop=vara--varb);

if _n_=1 then set table1;

set table2;

varaa=ifn(missing(varaa),vara,varaa);

varbb=ifn(missing(varbb),varb,varbb);

varcc=ifn(missing(varcc),varc,varcc);

run;

proc print;run;

Haikuo

Re: merge 1 row table to another column table

Haikuo — Fri, 20 Apr 2012 18:28:54 GMT

The array version of it:

data TABLE1;

input VarA VarB VarC;

cards;

34 45 13

;

data TABLE2;

input City$ VarAA VarBB VarCC;

cards;

Tor 64 34 12

Ott . 35 .

Mon 47 . 9

;

data want (drop=vara--varc);

if _n_=1 then set table1;

array t1 vara--varc;

set table2;

array t2 varaa--varcc;

do _n_=1 to dim(t1);

t2(_n_)=ifn(missing(t2(_n_)),t1(_n_),t2(_n_));

end;

run;

proc print;run;

Haikuo

Re: merge 1 row table to another column table

podarum — Fri, 20 Apr 2012 18:37:36 GMT

Thanks Haikuo, Great stuff..

Re: merge 1 row table to another column table

MikeZdeb — Fri, 20 Apr 2012 19:58:50 GMT

hi ... another idea ...

proc sql;

create table want as

select coalesce(varaa, vara) as n_vara, coalesce(varbb, varb) as n_varb, coalesce(varcc, varc) as n_varc

from table1, table2;

quit;

Re: merge 1 row table to another column table

podarum — Sat, 21 Apr 2012 04:53:41 GMT

Thanks Mike, this is a little cleaner..

Re: merge 1 row table to another column table

podarum — Sat, 21 Apr 2012 05:09:44 GMT

Is there a way I can include the newly created variables within the same procedure? I mean if I want to divide ((n_vara / n_varb)-1) as YoY

Thanks

Re: merge 1 row table to another column table

Haikuo — Sat, 21 Apr 2012 13:45:47 GMT

data want (drop=vara--varb);

if _n_=1 then set table1;

set table2;

varaa=ifn(missing(varaa),vara,varaa);

varbb=ifn(missing(varbb),varb,varbb);

varcc=ifn(missing(varcc),varc,varcc);

YoY=((varaa / varbb)-1);

run;

Re: merge 1 row table to another column table

MikeZdeb — Sat, 21 Apr 2012 15:57:49 GMT

hi, sure ...

proc sql;

create table want as

select coalesce(varaa, vara) as n_vara, coalesce(varbb, varb) as n_varb, coalesce(varcc, varc) as n_varc,

(calculated n_vara/calculated n_varb)-1 as yoy

from table1, table2;

quit;

Re: merge 1 row table to another column table

Howles — Sat, 21 Apr 2012 21:14:26 GMT

I try to let DATA step power do as much of the work as possible.

Test data:

data TABLE1;

input VarA VarB VarC;

cards;

34 45 13

;

data TABLE2;

input City$ VarA VarB VarC;

cards;

Tor 64 34 12

Ott . 35 .

Mon 47 . 9

;

Notice the use of the same names for corresponding variables in different tables. I usually find that advantageous.

Step 1 is to create serial numbers for the TABLE2 observations:

data numbered ;

Serial + 1 ;

set TABLE2 ;

run ;

This could be skipped if there were ordered keys in TABLE2. An alternative is to sort by city.

Next is the tricky bit, crossing the single observation in TABLE1 with the vector of keys:

data temp ;

set numbered(keep = Serial City) ;

if _n_ EQ 1 then set TABLE1 ;

run ;

The results looks like this:

Serial City VarA VarB VarC

1 Tor 34 45 13

2 Ott 34 45 13

3 Mon 34 45 13

Finish with a simple UPDATE:

data WANT ;

update temp numbered ;

by Serial ;

YoY= (VarA / VarB)-1 ;

run ;