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
... View more