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

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

"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:

image.png

 

 

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20
Please attach an expected output for better understandning of your issue.
Data never sleeps
DanD999
Quartz | Level 8

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
Super User Tom
Super User

"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:

image.png

 

 

DanD999
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Quartz | Level 8

Thanks for an alternative Kurt.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 461 views
  • 0 likes
  • 4 in conversation