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..
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
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
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
Thanks Haikuo, Great stuff..
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;
Thanks Mike, this is a little cleaner..
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
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;
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;
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 ;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.