topic Re: merge with index in SAS Programming

merge with index

sathya66 — Thu, 19 Apr 2018 12:08:43 GMT

Hi,

I am trying to merge with index variables but when merge finished the output table doesn't have the index table.

here my A table has index variables . I want see the same result in TEST table also(with index variables).

data test;

merge a(in=a) b(in=b);

by id;

if a=b then do;

if name ne ' ' then name='XXXX';

end;

if a then output;

run;

Re: merge with index

Kurt_Bremser — Thu, 19 Apr 2018 12:31:56 GMT

If you want to have an index for a newly created dataset, use proc datasets to add it to the dataset. Indexes are not automatically created.

Re: merge with index

RW9 — Thu, 19 Apr 2018 12:33:05 GMT

Post test data in the form of a datastep and what the output should look like. At a guess:

if a=b then do;

Does not do what you think it does, you may mean, if a and b, i.e. if record is in both datasets.

I can't really tell you anything else without seeing some data.

Re: merge with index

Kurt_Bremser — Thu, 19 Apr 2018 12:35:49 GMT

@RW9 wrote:

Post test data in the form of a datastep and what the output should look like. At a guess:
if a=b then do;
Does not do what you think it does, you may mean, if a and b, i.e. if record is in both datasets.

I can't really tell you anything else without seeing some data.

The funny thing is that this works, as a = b = 0 can never happen. Although I prefer to use if a and b;

Re: merge with index

sathya66 — Thu, 19 Apr 2018 12:43:21 GMT

yes It is working , but I will change to if a and b. here Id is the index varable

Re: merge with index

sathya66 — Thu, 19 Apr 2018 12:45:37 GMT

sorry,
here base table is A table and A table is B table

Re: merge with index

RW9 — Thu, 19 Apr 2018 12:48:21 GMT

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: merge with index

Kurt_Bremser — Thu, 19 Apr 2018 12:54:04 GMT

Running this:

data test;
merge sascomm.base(in=a) sascomm.a(in=b);
by id;
if a=b then do;
  if name ne ' ' then name='XXXX';
end;
if a then output;
run;

proc print data=sascomm.base noobs;
proc print data=sascomm.a noobs;
proc print data=test noobs;
run;

after uploading your datasets, I see no problems at all:

  id    name

   1    qq   
   2    aa   
   3    ss   
   4    ss   
   5    dd   
   6    dff  
   7    vff  
   8    gggg 
   9    ggggg
  10    rrr  
  11    sss  
  12    ssss 
  13    dddd 
  14    sssss
                

       id

        2
        5
        6
        7
       10
                

  id    name

   1    qq   
   2    XXXX 
   3    ss   
   4    ss   
   5    XXXX 
   6    XXXX 
   7    XXXX 
   8    gggg 
   9    ggggg
  10    XXXX 
  11    sss  
  12    ssss 
  13    dddd 
  14    sssss

Re: merge with index

sathya66 — Thu, 19 Apr 2018 13:01:11 GMT

yes, sascomm.base table is an index table on ID.(didn't upload index table).
when we perform merge test table doesn't create an index table.

Re: merge with index

sathya66 — Thu, 19 Apr 2018 13:10:17 GMT

data sascomm.base(index=(id));

set sascomm.base;

run;

data test;
merge sascomm.base(in=a) sascomm.a(in=b);
by id;
if a=b then do;
  if name ne ' ' then name='XXXX';
end;
if a then output;
run;

when we perform this. test table is not creating the index table . I want to get the index table also.

Re: merge with index

Kurt_Bremser — Thu, 19 Apr 2018 13:15:16 GMT

As I already said, use proc datasets to create an index table for a newly created dataset. Index tables are not automatically created.