SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Many to few merge

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Many to few merge

Hello All,

I have a somewhat basic question about many-to-few merge in SAS. I am trying to merge two datasets in SAS with unequal number of by-groups in the datasets. I am trying to merge by id without the last records in dataset2 getting carried over to the output dataset. The two input datsets and the planned output dataset are as follows:

Dataset1:                                                     

iddaycomputer_score
1119
1222
1313
1414
1517
219
228
237
248
259

Dataset2:                       

idverbal_score
115
117
119
27
29

Output dataset:                                                                

iddaycomputer_scoreverbal_score
111915
122217
131319
1414
1517
219
2289
237
248
259

Any help will be much appreciated.

Thanks,

Pronabesh


Accepted Solutions
Solution
‎12-01-2013 02:10 PM
PROC Star
Posts: 7,487

Re: Many to few merge

Posted in reply to pronabesh

If that 9 for the first record for id=2 should be a seven, then I think that your really only have a one-to-one match.

Try:

data Dataset2;

  set Dataset2;

  by id;

  if first.id then day=1;

  else day+1;

run;

data want;

  merge dataset1 (in=a) dataset2;

  by id day;

  if a;

run;

View solution in original post


All Replies
Contributor
Posts: 58

Re: Many to few merge

Posted in reply to pronabesh

One correction:

The output dataset should look like this:

iddaycomputer_scoreverbal_score
111915
122217
131319
1414
1517
2199
2288
237
248
259
Solution
‎12-01-2013 02:10 PM
PROC Star
Posts: 7,487

Re: Many to few merge

Posted in reply to pronabesh

If that 9 for the first record for id=2 should be a seven, then I think that your really only have a one-to-one match.

Try:

data Dataset2;

  set Dataset2;

  by id;

  if first.id then day=1;

  else day+1;

run;

data want;

  merge dataset1 (in=a) dataset2;

  by id day;

  if a;

run;

Contributor
Posts: 58

Re: Many to few merge

Hello Arthur,

Thank for the response. This worked perfectly for the mock datasets. However, there is another layer to my problem. The day variable in dataet1 is actually a date variable. There is no date for dataset2 ( not the same examination date as dataset1 anyway). However, I need to merge the scores from dataset2 to dataset1 one-to-one and not carry it forward if there are less than 5 observations per subject. Also the dates can change between and within subjects. The datasets are as follows:

Dataset1:                                                    

iddatecomputer_score
11/1/1319
12/1/1322
13/1/1313
14/1/1314
15/1/1317
21/1/139
22/1/138
23/1/137
24/1/138
25/1/139

Dataset2:                      

idverbal_score
115
117
119
27
29

Output dataset:                                                               

iddaycomputer_scoreverbal_score
11/1/131915
12/1/132217
13/1/131319
14/1/1314
15/1/1317
21/1/139                7
22/1/1389
23/1/137
24/1/138
25/1/139
PROC Star
Posts: 7,487

Re: Many to few merge

Posted in reply to pronabesh

Your example data and shown desired output file was the same as the original one, which is what my previous offered code accomplished, thus you have to be clearer regarding what you want.

Checking for number of records is easy, as is sorting the file by date within id.  The questions are checking in which file\(s), what to do when there are less than five records, whehter you do need to carry forward any of the scores, and whether the dates themselves are relevant to your needed solution?

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 395 views
  • 0 likes
  • 2 in conversation