Getting error while using merge SAS table in SAS-BASE programming

[sh4529]

Hello Everyone,

I am trying to use merge SAS table (table_two and table_bin)

In my table_two ,I have few rows with a garbage value for text column,  which I want to delete. But I don't want to loose the structure of table.
I created another table  table_bin, which is list of all garbage values in text column. I tried to merge the table where if it is in table_bin it should get deleted from main table table_two.

table_two
idx_num  text  count  color 
1             one     5        red
1            Two     5        red
1            aa        5        red
1           cd         5        red
1           Three   5         red
2           Two      5        green
2            bb        5       green
2            Four     5       green
2             ac        5       green
2             ce        5       green

table_bin
text
aa
ab
ac
bb
bc
bd
cc
cd
ce

o/p
table_two
idx_num  text  count  color 
1              one    5       red
1              Two    5      red

1             Three  5      red
2            Two      5     green

2             Four    5     green

proc sort table_two;
   by  idx_num text;
run;

proc sort table_bin;
   by  text;
run;

data table_clean;
  merge table_two(in=inP) table_bin (in=inS);
  by text
  if inP=1 and inS=0;
run;

WARNING: Multiple lengths were specified for the BY variable text by input data sets.

This might cause unexpected results.

ERROR: BY variables are not properly sorted on data set WORK.TABLE_CLEAN.

I am getting this warning and error and then it stopped with the output of empty table.

Please can you help and if possible explain, where I am going wrong

Thanks

Shalini

[Reeza]

proc sort table_two;
   by  idx_num text;
run;

 

proc sort table_bin;
   by  text;
run;

 

data table_clean;
  merge table_two(in=inP) table_bin (in=inS);
  by text
  if inP=1 and inS=0;
run;

1. You're missing a semicolon on your BY statement in the MERGE

2. You're sorting by ID and TEXT, so TEXT will not be sorted correctly for the merge. Your BY statement must be the same in the SORT as in the MERGE is a good rule of thumb when merging. 

---

@sh4529 wrote:

Hello Everyone,

I am trying to use merge SAS table (table_two and table_bin)

In my table_two ,I have few rows with a garbage value for text column,  which I want to delete. But I don't want to loose the structure of table.
I created another table  table_bin, which is list of all garbage values in text column. I tried to merge the table where if it is in table_bin it should get deleted from main table table_two.

table_two
idx_num  text  count  color 
1             one     5        red
1            Two     5        red
1            aa        5        red
1           cd         5        red
1           Three   5         red
2           Two      5        green
2            bb        5       green
2            Four     5       green
2             ac        5       green
2             ce        5       green

table_bin
text
aa
ab
ac
bb
bc
bd
cc
cd
ce

o/p
table_two
idx_num  text  count  color 
1              one    5       red
1              Two    5      red

1             Three  5      red
2            Two      5     green

2             Four    5     green

proc sort table_two;
   by  idx_num text;
run;

proc sort table_bin;
   by  text;
run;

data table_clean;
  merge table_two(in=inP) table_bin (in=inS);
  by text
  if inP=1 and inS=0;
run;

WARNING: Multiple lengths were specified for the BY variable text by input data sets.

This might cause unexpected results.

ERROR: BY variables are not properly sorted on data set WORK.TABLE_CLEAN.

I am getting this warning and error and then it stopped with the output of empty table.

Please can you help and if possible explain, where I am going wrong

Thanks

Shalini

---