topic Re: One-to-many Merge with a Twist in SAS Programming

One-to-many Merge with a Twist

rvsidhu035 — Fri, 16 Mar 2018 06:37:16 GMT

i am attached sas editor file below
Every id visit in three times each visit conduct four test a b c d in every vist
and second data "b" give three vist of date.
how merge date varable of data b with data a dataset.
i want like this
1 a 1 aa
1 b 2 aa
1 c 1 aa
1 d 1 aa
1 a . bb
1 b 2 bb
1 c . bb
1 d 2 bb
.... like this;
data a;
input id test$ sev @@;
datalines;
1 a 1 1 b 2 1 c 1 1 d 1
1 a . 1 b 2 1 c . 1 d 2
1 a 2 1 b 2 1 c 1 1 d 1
2 a 1 2 b . 2 c 1 2 d 2
2 a 1 2 b 1 2 c . 2 d 2
2 a 2 2 b 1 2 c 1 2 d 1
3 a 1 3 b 1 3 c 1 3 d 2
3 a 2 3 b 2 3 c 2 3 d 1
3 a . 3 b 1 3 c 1 3 d 2
;
run;

data b;
input id date$ @@;
datalines;
1 aa 1 bb 1 cc
2 dd 2 ee 2 ff
3 ee 3 gg 3 df
;
run;

Re: One-to-many Merge with a Twist

Kurt_Bremser — Fri, 16 Mar 2018 06:37:34 GMT

As long as you can be sure that there's always four entries in A for every entry in B, then this will do it:

data want;
set b;
do i = 1 to 4;
  set a;
  output;
end;
drop i;
run;

A more robust version uses transpose:

data int1;
set a;
by id;
if first.id then visit = 0;
if test = 'a' then visit + 1;
run;

proc transpose data=int1 out=int2 (drop=_name_);
var sev;
id test;
by id visit;
run;

data int3;
set b;
by id;
if first.id
then visit = 1;
else visit + 1;
run;

data int4;
merge
  int2
  int3
;
by id visit;
run;

data want (keep=id test sev date);
set int4;
array vars {4} a b c d;
do i = 1 to 4;
  test = vname(vars{i});
  sev = vars{i};
  output;
end;
run;