topic Re: Merge two datasets with creation of an identifier in SAS Programming

Merge two datasets with creation of an identifier

_MVB_ — Fri, 19 Jun 2020 20:00:04 GMT

Hi All!

Looking for a quick and short solution (a replacement to a 2-3 steps logic I have), where I want to use a data step which will include merging two datasets (exactly same variables on both), and while merging also create a new variable called identifier which will specify whether a record coming from dataset1 or dataset2 on the output dataset.

Re: Merge two datasets with creation of an identifier

smantha — Fri, 19 Jun 2020 20:07:42 GMT

data output;
merge a(in=ina) b(in=inb);
by <byvar list>;
if ina and inb then exists='inainb';
else if ina then exists ='ina';
else if inb then exists ='inb';
run;

Re: Merge two datasets with creation of an identifier

_MVB_ — Fri, 19 Jun 2020 20:14:51 GMT

@smantha Thanks for a quick reply.

I think I need a slightly different logic, as I do not need to check whether a record is in the first/second/both datasets.

I know I have exactly same records on both datasets, as second is a reduced dataset created from first dataset.

I simply need to create an indicator while merging the two datasets, so if the record coming from dataset1 it will mark it as A, but if a record coming from dataset2 it will mark it as B, and put it on a newly created variable.

Hope this clarifies better.

Re: Merge two datasets with creation of an identifier

Reeza — Fri, 19 Jun 2020 20:19:50 GMT

Are you merging or appending? Usually with the same variables its an append and then you can use INDSNAME option.

data want;
set have1 have2 indsname= source;
data_source = source;
run;

Re: Merge two datasets with creation of an identifier

ballardw — Fri, 19 Jun 2020 20:21:12 GMT

It would help to show the code you are currently using.

The dataset option IN= creates a variable indicating that a record came from that dataset

Pseudo example:

data garbage;
   merge set1 (in=in1)
              set2 (in=in2)
  ;
  if in1 and in2 then source='Both';
  else if in1 then source = 'set1';
  else if in2 then source ='set2';
run;

I have to assume your merge would have some by variable(s) but that wouldn't make a difference in the options and if/then/else code.

Re: Merge two datasets with creation of an identifier

_MVB_ — Fri, 19 Jun 2020 20:45:03 GMT

@Reeza combining two datasets

on the dataset2 I have exactly same records and on dataset1, the difference in between the two is that dataset1 has several times more records, and all of the records on dataset2 would be found on dataset1.

I do not need to check if any of the records are on both or only one dataset, but simply combine the two dataset and have a new variable created which would specify either record is coming from dataset1 or dataset2.

Re: Merge two datasets with creation of an identifier

_MVB_ — Fri, 19 Jun 2020 20:48:46 GMT

This is what I have now:

- dataset1 has 10,000 records

- dataset2 has 1,000 records

data dataset1;

set dataset1;

format ds $1.;

ds='A';

run;

data dataset2;

set dataset2;

format ds $1.

ds='B';

run;

data merged;

set dataset1 dataset2;

run;

Final 'merged' dataset has 11,000 records with one new variable 'ds' which has 'A' for the 10,000 records and 'B' for other 1,000 records.

Re: Merge two datasets with creation of an identifier

smantha — Fri, 19 Jun 2020 21:00:13 GMT

In here you are not merging the datasets but concatenating one to the bottom of the other. What about the overlapping records?

Re: Merge two datasets with creation of an identifier

_MVB_ — Fri, 19 Jun 2020 21:06:27 GMT

@smantha yes, I should have used a concatenating, not merging.

Overlapping records are not an issue, there is a purpose of why these 1,000 records from dataset2 needed on the final dataset. They would be exactly same, because they were outputted in earlier steps from the dataset1. I just need a final dataset to have 11,000 records with an indicator which would be used later to differentiate whether it is coming from dataset1 or dataset 2.

Re: Merge two datasets with creation of an identifier

Reeza — Fri, 19 Jun 2020 21:09:40 GMT

@_MVB_ wrote:

@Reeza combining two datasets

on the dataset2 I have exactly same records and on dataset1, the difference in between the two is that dataset1 has several times more records, and all of the records on dataset2 would be found on dataset1.

I do not need to check if any of the records are on both or only one dataset, but simply combine the two dataset and have a new variable created which would specify either record is coming from dataset1 or dataset2.

Did you run it and try it?

Re: Merge two datasets with creation of an identifier

Patrick — Fri, 19 Jun 2020 21:12:12 GMT

@_MVB_ wrote:

@smantha yes, I should have used a concatenating, not merging.

Overlapping records are not an issue, there is a purpose of why these 1,000 records from dataset2 needed on the final dataset. They would be exactly same, because they were outputted in earlier steps from the dataset1. I just need a final dataset to have 11,000 records with an indicator which would be used later to differentiate whether it is coming from dataset1 or dataset 2.

Then look again at what @Reeza already proposed because that's doing what you're asking for.

Re: Merge two datasets with creation of an identifier

_MVB_ — Fri, 19 Jun 2020 21:53:20 GMT

@Reeza it works, however I do not want to have a work.database1 or work.database2 in my output dataset. I can, of course put an extra line in that procedure where specify if work.database1 then A, else if work.database2 then B, but isn't there another way just to specify either A or B without IF THEN statement?

Re: Merge two datasets with creation of an identifier

Reeza — Fri, 19 Jun 2020 21:59:23 GMT

Then use the IN approach which gives you an indicator variable instead. If you absolutely, 100% require A, B then use IF/THEN. There are automated ways to do it, but if your requirements don't align then you need to use IF/THEN. You need to make sure it handles multiple data sets as well.

Re: Merge two datasets with creation of an identifier

Patrick — Fri, 19 Jun 2020 22:25:12 GMT

@_MVB_ wrote:

@Reeza it works, however I do not want to have a work.database1 or work.database2 in my output dataset. I can, of course put an extra line in that procedure where specify if work.database1 then A, else if work.database2 then B, but isn't there another way just to specify either A or B without IF THEN statement?

May be we get faster to your desired result using sample data.

With below sample data how should the desired result look like and how does it differ from what below code generates?

data a;
  do i=1 to 10;
    output;
  end;
  stop;
run;
data b;
  set a;
  if i in (1,5,7);
run;

data want;
  length _inds inds $41;
  set a b indsname=_inds;
  inds=_inds;
run;

proc print data=want;
run;

Re: Merge two datasets with creation of an identifier

_MVB_ — Sat, 20 Jun 2020 00:18:15 GMT

In the end, I modified solution proposed by @Reeza just adding IF THEN statement to replace work.DATASET1 and work.DATASET2 fields with A or B.

Re: Merge two datasets with creation of an identifier

Patrick — Sat, 20 Jun 2020 00:28:28 GMT

Thanks for the feedback.

Please mark one of @Reeza's answers as solution so that this discussion gets closed.

Re: Merge two datasets with creation of an identifier

Reeza — Sat, 20 Jun 2020 16:21:55 GMT

data want;
set have (in=a1) have2 (in=a2);
source = ifn(a1=1, 'A', 'B');
run;

Re: Merge two datasets with creation of an identifier

_MVB_ — Wed, 24 Jun 2020 16:52:59 GMT

@Reeza Thanks!

This is what I was looking for in terms of the short solution.