The queries that I use for facl and nonfacl may or may not return any rows. Notice that the temp88 query uses a hsc_id and a seq_nbr as the join where as the temp7 query only uses the hsc_id. So right now the last query, temp88, does not update the prg_type_cd and risk columns.
I've thought about adding the two blank columns to the mbrs table initially and then using an update in the proc sql, but is there a better, simpler way to do this?
Thanks
data mbrs;
infile datalines delimiter=',';
input hsc_id seq_nbr;
datalines;
188789846,1
188835206,2
188985878,1
189414866,1
189414777,3
189415555,1
;
run;
data facl;
length prg_type_cd risk $ 20;
infile datalines delimiter=',';
input hsc_id prg_type_cd $ risk $ ;
datalines;
188789846,ACUTE HOSPITAL,SURGICAL
188835206,OUTPATIENT FACILITY,SURGICAL1
188985878,ACUTE HOSPITAL,SURGICAL2
189414866,OUTPATIENT FACILITY,SURGICAL3
;
run;
proc sql;
create table temp7 as
select mbrs.*, facl.prg_type_cd, facl.risk
from mbrs
left join facl
on facl.hsc_id=mbrs.hsc_id
;
quit;
data nonfacl;
length prg_type_cd risk $ 20;
infile datalines delimiter=',';
input hsc_id seq_nbr prg_type_cd $ risk $ ;
datalines;
189414777,3,ACUTE HOSPITAL,SURGICAL4
189415555,1,OUTPATIENT FACILITY,SURGICAL4
;
run;
proc sql;
create table temp88 as
select mbrs.*, nonfacl.prg_type_cd, nonfacl.risk
from temp7 mbrs
left join nonfacl
on nonfacl.hsc_id=mbrs.hsc_id
and nonfacl.seq_nbr=mbrs.seq_nbr
;
quit;
... View more
