DATA Step, Macro, Functions and more

Merging Data sets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

Merging Data sets

I have a data set1:

ID TIME PRED      new_study
1       0       0              1
1     0.25    1.1063    1
1    0.5       5.1534    1
2      0           0          1
2    0.25    1.0462     1
2     0.5      4.8104    1
1     0          0            2
1    0.25     1.1063    2
1   0.5         5.1534   2
2    0            0           2
2    0.25     1.0462    2
2    0.5        4.8104   2

and a data set2

id time       ratio
1      0        0
1     0.25     0.937629938
1     0.5       0.925815966
2      0          0
2    0.25      0.991493022
2    0.5          0.991830201

I would like to merge these data sets into a data set final (example below) in which the same ratio values from dataset2 are repeated for each subject in each new_study

 

ID     TIME     PRED      new_study      ratio
1          0          0                 1                  0
1         0.25  1.1063            1                 0.937629938
1           0.5       5.1534       1                 0.925815966
2           0           0               1                 0
2           0.25   1.0462         1                 0.991493022
2          0.5      4.8104         1                  0.991830201
1           0            0              2                  0
1            0.25   1.1063        2                  0.937629938
1           0.5       5.1534       2                  0.925815966
2           0           0              2                   0
2           0.25  1.0462          2                 0.991493022
2            0.5     4.8104       2                  0.991830201

 

I have tried some merges but each time the study and ratios get sorted which is not what I want.  I need the newstudy and ratio values not to be sorted.  I have included my simple merge code.

proc sort data=data1; by id time;run;
proc sort data=data2; by id time; run;


Data final;
merge data1 data2;
by id time;
run;

Accepted Solutions
Solution
‎10-09-2017 05:55 PM
PROC Star
Posts: 1,570

Re: Merging Data sets

Posted in reply to Astounding

data have;

input ID TIME PRED      new_study;

datalines;

1       0       0              1

1     0.25    1.1063    1

1    0.5       5.1534    1

2      0           0          1

2    0.25    1.0462     1

2     0.5      4.8104    1

1     0          0            2

1    0.25     1.1063    2

1   0.5         5.1534   2

2    0            0           2

2    0.25     1.0462    2

2    0.5        4.8104   2

;

 

 

data have1;

input id time       ratio;

datalines;

1      0        0

1     0.25     0.937629938

1     0.5       0.925815966

2      0          0

2    0.25      0.991493022

2    0.5          0.991830201

;

 

 

 

data final;

   if _N_ = 1 then do;

      if 0  then do;

       set have;

     set have1;

      end;

      declare hash myhash(dataset:'have1', multidata:'yes' );

     myhash.defineKey('id','time');

     myhash.defineData('ratio');

     myhash.defineDone( );

       end;

set have;

if myhash.find() ne 0 then ratio=0;

run;

 

View solution in original post


All Replies
PROC Star
Posts: 1,570

Re: Merging Data sets

Posted in reply to jacksonan123

Most simple is hash find method. Is it ok for you to implement hash?

Super User
Posts: 6,629

Re: Merging Data sets

Posted in reply to jacksonan123

So far, you're doing the right thing (although using PROC SQL might simplify the process).  All  you are missing is a final PROC SORT to put the data back into the right order:

 

proc sort data=final;

by new_study id time;

run;

Solution
‎10-09-2017 05:55 PM
PROC Star
Posts: 1,570

Re: Merging Data sets

Posted in reply to Astounding

data have;

input ID TIME PRED      new_study;

datalines;

1       0       0              1

1     0.25    1.1063    1

1    0.5       5.1534    1

2      0           0          1

2    0.25    1.0462     1

2     0.5      4.8104    1

1     0          0            2

1    0.25     1.1063    2

1   0.5         5.1534   2

2    0            0           2

2    0.25     1.0462    2

2    0.5        4.8104   2

;

 

 

data have1;

input id time       ratio;

datalines;

1      0        0

1     0.25     0.937629938

1     0.5       0.925815966

2      0          0

2    0.25      0.991493022

2    0.5          0.991830201

;

 

 

 

data final;

   if _N_ = 1 then do;

      if 0  then do;

       set have;

     set have1;

      end;

      declare hash myhash(dataset:'have1', multidata:'yes' );

     myhash.defineKey('id','time');

     myhash.defineData('ratio');

     myhash.defineDone( );

       end;

set have;

if myhash.find() ne 0 then ratio=0;

run;

 

Frequent Contributor
Posts: 130

Re: Merging Data sets

Posted in reply to novinosrin
It did give me the result that I sought except that it only did so for
new_study, new_study 2 was not output.
Frequent Contributor
Posts: 130

Re: Merging Data sets

Posted in reply to novinosrin

I made an error in coding in your response.  When corrected it worked perfectly by listing all of the studies..

 

Thanks for the help.

Frequent Contributor
Posts: 130

Re: Merging Data sets

Posted in reply to Astounding

It performed the sort as you stated but it only output new_study1 there was no new_study 2 output.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 145 views
  • 0 likes
  • 3 in conversation