topic Re: How to add data to a dataset in SAS Programming

How to add data to a dataset

DanD999 — Wed, 17 Nov 2021 15:53:08 GMT

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;

Re: How to add data to a dataset

LinusH — Wed, 17 Nov 2021 16:10:33 GMT

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

Re: How to add data to a dataset

DanD999 — Wed, 17 Nov 2021 16:22:34 GMT

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

Re: How to add data to a dataset

Tom — Wed, 17 Nov 2021 16:45:48 GMT

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

Re: How to add data to a dataset

Kurt_Bremser — Wed, 17 Nov 2021 17:28:08 GMT

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;

Re: How to add data to a dataset

DanD999 — Wed, 17 Nov 2021 17:51:03 GMT

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

Re: How to add data to a dataset

DanD999 — Wed, 17 Nov 2021 17:52:10 GMT

Thanks for an alternative Kurt.