topic Re: Creating two new variables by comparing the obs in two tables in SAS Programming

Creating two new variables by comparing the obs in two tables

Ranjeeta — Fri, 17 Jan 2020 19:30:24 GMT

Can Someone please advise how I can get the final output using Table 1 and Table 2

Thankyou

Re: Creating two new variables by comparing the obs in two tables

novinosrin — Fri, 17 Jan 2020 19:42:33 GMT

Are you asking for an inner join?

data have1;
infile cards truncover;
input INST	FAC_NUM;
cards;
4265	948
4266	948
;

data have2;
input INST_AM	INST_AT	FAC_NUM;
cards;
4265	4266	948
;

proc sql;
create table want as
select a.*,INST_AT
from have1 a,have2 b
where a.inst=b.inst_am and a.fac_num=b.fac_num;
quit;

Re: Creating two new variables by comparing the obs in two tables

Ranjeeta — Fri, 17 Jan 2020 19:59:34 GMT

Thankyou but I need two columns the INST_AT as well so how would I modify the code

Also I need to have the repeated row deleted and get the final output as in the EXCEl

proc sql;
create table want as
select a.*,INST_AM,INST_AT
from new a,inst_trans b
where a.id=b.inst_am and a.fac_num=b.fac_num
or a.id=b.inst_at and a.fac_num=b.fac_num ;
quit;

Re: Creating two new variables by comparing the obs in two tables

novinosrin — Fri, 17 Jan 2020 20:11:35 GMT

Not sure what you are saying. When i ran -

proc sql;
create table want as
select a.*,INST_AT
from have1 a,have2 b
where a.inst=b.inst_am and a.fac_num=b.fac_num;
quit;

proc print noobs;run;

I get this.

INST	FAC_NUM	INST_AT
4265	948	4266