Help using Base SAS procedures

merge 1 row table to another column table

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

merge 1 row table to another column table

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..


Accepted Solutions
Solution
‎04-20-2012 02:22 PM
Respected Advisor
Posts: 3,124

Re: merge 1 row table to another column table

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

View solution in original post


All Replies
Solution
‎04-20-2012 02:22 PM
Respected Advisor
Posts: 3,124

Re: merge 1 row table to another column table

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

Respected Advisor
Posts: 3,124

Re: merge 1 row table to another column table

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

Super Contributor
Posts: 395

Re: merge 1 row table to another column table

Thanks Haikuo, Great stuff..

Valued Guide
Posts: 765

Re: merge 1 row table to another column table

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;

Super Contributor
Posts: 395

Re: merge 1 row table to another column table

Thanks Mike, this is a little cleaner..

Super Contributor
Posts: 395

Re: merge 1 row table to another column table

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

Respected Advisor
Posts: 3,124

Re: merge 1 row table to another column table

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;

Valued Guide
Posts: 765

Re: merge 1 row table to another column table

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;

Regular Contributor
Posts: 184

Re: merge 1 row table to another column table

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 ;

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 348 views
  • 0 likes
  • 4 in conversation