topic Re: How to add variables from a different dataset by ID in SAS Programming

How to add variables from a different dataset by ID

jspoend — Tue, 01 Feb 2022 10:56:14 GMT

Hi there,

I have two datasets, and I want to add variables from 1 dataset to the other.

Dataset_A has several lines per patient ID

ID drug date_of_claim ATC

1 a b c

1 e f g

2 x y z

3 z r t

3 r t g

Dataset_B has 1 line per ID

ID start_insurance end_insurance

1 date1 date2

2 date3 date4

I want to add the two varibales 'start_insurance' and 'end_insurance' from Dataset_B to Dataset_A, in a ways that every line in Dataset_A will have values for the dates.

I'm not sure how to go about this, so any input is very welcome.

Thanks very much in advance, Julia

Re: How to add variables from a different dataset by ID

PeterClemmensen — Tue, 01 Feb 2022 11:00:18 GMT

Try this

data a;
input ID (drug date_of_claim ATC)($);
datalines;
1 a b c
1 e f g
2 x y z
3 z r t
3 r t g
;

data b;
input ID (start_insurance end_insurance)(:date9.);
format start_insurance end_insurance date9.;
datalines;
1 01feb2022 02feb2022
2 03feb2022 04feb2022
;

data want;
   merge a b;
   by ID;
run;

Re: How to add variables from a different dataset by ID

Kurt_Bremser — Tue, 01 Feb 2022 11:50:07 GMT

You won't be able to get values where you have no match in B, but you can also use the hash method I showed in your other thread (using the datasets provided by @PeterClemmensen 😞

data want;
set a;
if _n_ = 1
then do;
  format start_insurance end_insurance date9.;
  declare hash b (dataset:"b");
  b.definekey("id");
  b.definedata("start_insurance","end_insurance");
  b.definedone();
end;
if b.find() ne 0
then do;
  start_insurance = .;
  end_insurance = .;
end;
run;

Re: How to add variables from a different dataset by ID

jspoend — Tue, 01 Feb 2022 12:35:46 GMT

Dear Kurt,

Thank you so much for the second time today, your command worked perfectly again, and I will know how to adjust it now as well.

Have a great day, Julia

Re: How to add variables from a different dataset by ID

jspoend — Tue, 01 Feb 2022 12:37:16 GMT

Hi Peter,
Thanks a lot for sending this, I've tried with merge before, but I was worried it would overwrite anything. But I guess it should be fine if there are no overlapping variables.

Many thanks, Julia