How to add data to a dataset

[DanD999]

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;

[LinusH]

Please attach an expected output for better understandning of your issue.

[DanD999]

Expected output.

hsc_id	seq_nbr	prg_type_cd	risk
188789846	1	ACUTE HOSPITAL	SURGICAL
188835206	2	OUTPATIENT FACILITY	SURGICAL1
188985878	1	ACUTE HOSPITAL	SURGICAL2
189414777	3	ACUTE HOSPITAL SURGICAL4	
189414866	1	OUTPATIENT FACILITY	SURGICAL3
189415555	1	OUTPATIENT FACILITY SURGICAL4	

[Tom · Accepted solution]

"add data to a dataset" is not a normal analysis process.

Sounds like you want to make a NEW dataset that has the information from those three input datasets.

Since the keys are different just use multiple steps. (make sure the sources are sorted by the key variables).

data step1 ;
  merge mbrs facl;
  by hsc_id;
run;

proc print;
 title 'step1';
run;

data step2;
  merge step1 nonfacl;
  by hsc_id seq_nbr;
run;

proc print;
 title 'step2';
run;
title;

Results:

[DanD999]

I thought that there must be a simpler way. I didn't think of using merge instead of proc sql. Thanks Tom.

[Kurt_Bremser]

You can do it in one data step through the use of hash objects:

data want;
set mbrs;
length prg_type_cd risk $ 20 seq_nbr 8;
if _n_ = 1
then do;
  declare hash facl (dataset:"facl");
  facl.definekey("hsc_id");
  facl.definedata("prg_type_cd","risk");
  facl.definedone();
  declare hash nonfacl (dataset:"nonfacl");
  nonfacl.definekey("hsc_id","seq_nbr");
  nonfacl.definedata("prg_type_cd","risk");
  nonfacl.definedone();
end;
rc = facl.find();
rc = nonfacl.find();
drop rc;
run;

[DanD999]

Thanks for an alternative Kurt.