BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
podarum
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

9 REPLIES 9
Haikuo
Onyx | Level 15

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

Haikuo
Onyx | Level 15

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

podarum
Quartz | Level 8

Thanks Haikuo, Great stuff..

MikeZdeb
Rhodochrosite | Level 12

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;

podarum
Quartz | Level 8

Thanks Mike, this is a little cleaner..

podarum
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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;

MikeZdeb
Rhodochrosite | Level 12

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;

Howles
Quartz | Level 8

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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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