Hi Radha009   This works for me. The only difference seems to be the ODS destination.    filename mailbox email subject="Mysubject" from="sender@company.com" to=("sender@company.com") type="text/html"; ods listing close; ods MSOffice2K body=mailbox style=HTMLblue; proc print data=sashelp.class; run; ods _all_ close; ods listing; ... View more

Hi KurtBremser   After running a full-scale test, I still advocate the sql approach.   I made a score data set with 8000 different words, and a string data set with 6 million strings containing from 3 to 8 words, of wich zero to 8 were taken from the from the score list.   As you can se in the following log, the crucial part, joining 24 million words from the "outfolded" strings with the score list, used 7 cpu seconds and took 12 seconds in lapse time, and the whole operation took less than 25 seconds in lapse time.   The hash object approach is certainly more elegant in terms of coding, and it might even be a few seconds faster, but the brute force way is a lot easier ti explain and debug.     565 * 0. Add ID variable; 566 data wteststrings1; set test_strings; 567 ID = _N_; 568 run; NOTE: There were 6000000 observations read from the data set WORK.TEST_STRINGS. NOTE: The data set WORK.WTESTSTRINGS1 has 6000000 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.47 seconds cpu time 0.48 seconds 569 570 * 1. Split test_strings in words - keep only ID and word ; 571 data wteststrings2 (keep=ID word); set wteststrings1; 572 ID = _N_; 573 length word $4; 574 do i = 1 to countw(str); 575 word = scan(str,i,' '); 576 output; 577 end; 578 run; NOTE: There were 6000000 observations read from the data set WORK.WTESTSTRINGS1. NOTE: The data set WORK.WTESTSTRINGS2 has 24114600 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 1.99 seconds cpu time 2.00 seconds 579 580 * 2. Join input and score datasets to get a score for each word; 581 proc sql; 582 create table scores1 as 583 select 584 a.ID, 585 a.word, 586 b.score 587 from wteststrings2 as a 588 left join score_values as b 589 on a.word = b.word 590 where b.score > 0; NOTE: Table WORK.SCORES1 created, with 13044600 rows and 3 columns. 591 quit; NOTE: PROCEDURE SQL used (Total process time): real time 6.93 seconds cpu time 12.18 seconds 592 593 * Summarize scores pr. ID; 594 proc sql; 595 create table scores2 as 596 select distinct 597 ID, 598 sum(score) as score 599 from scores1 600 group by ID; NOTE: Table WORK.SCORES2 created, with 4800000 rows and 2 columns. 601 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.28 seconds cpu time 6.30 seconds 602 603 * Join sum back on input to include strings without any score; 604 proc sql; 605 create table result as 606 select 607 a.ID, 608 a.str, 609 max(b.score,0) as score 610 from wteststrings1 as a left join scores2 as b 611 on a.ID = b.ID; NOTE: Table WORK.RESULT created, with 6000000 rows and 3 columns. 612 quit; NOTE: PROCEDURE SQL used (Total process time): real time 3.00 seconds cpu time 3.86 seconds ... View more

Hi Ullsokk   In my experience SQL join works pretty fast, so here is a different approach. I don't know if it can compete with the hash look-up method in speed, but it is very simple and easy to understand and explain, and optimizing is easy, ex by cleaning common and uninteresting words out in step 2 to reduce the size of the join data set.   /*----------------------- create test data ----------------------*/ * TEST score dataset; data score_values; test = 'AAA'; score = 5; output; test = 'BBB'; score = 3; output; test = 'CCC'; score = 4; output; run; * TEST input data set - must contain ID-variable; data test_strings; length ID 8 str $32; ID = 1; str = 'He stays at AAA'; output; ID = 2; str = 'He drove from AAA to BBB'; output; ID = 3; str = 'He drove from CCC to DDD'; output; ID = 4; str = 'He moved to DDD'; output; ID = 5; str = 'He drove from AAA to CCC via BBB'; output; ID = 6; str = 'AAA and AAA and AAA'; output; run; /*------------------------- processing --------------------------*/ * 1. Split test_strings in words - keep only ID and word ; data wteststrings (keep=ID word); set test_strings; length word $12; do i = 1 to countw(str); word = scan(str,i,' '); output; end; run; * 2. Join input and score datasets to get a score for each word; proc sql; create table scores1 as select a.ID, a.word, b.score from wteststrings2 as a left join score_values as b on a.word = b.test where b.score > 0; quit; * Summarize scores pr. ID; proc sql; create table scores2 as select distinct ID, sum(score) as score from scores1 group by ID; quit; * Join sum back on input to include strings without any score; proc sql; create table result as select a.ID, a.str, max(b.score,0) as score from wteststrings1 as a, scores2 as b where a.ID = b.ID; quit; ... View more

Hi As I understand your problem, it is because the input data is not sorted on y and x.   The supplied code gives 2 output records: Y1 X1 and Y2 X2   data have; input Y$ X$; datalines; y1 x1 y2 x2 y1 x3 y1 x4 y1 x5 y2 x6 ; run; proc sort data=have; by Y X; run; data want; set have; by Y; retain wantx; if first.y then wantx = x; if last.y then output; run;   ... View more

Hi Albert0   As far as I know the table's metadata entry is deleted if the corresponding physical table is NOT found. If the table exists, the medata entry for the table's columns are updated to be in sync with the actual physical columns. ... View more