How are you importing the data into Access? Is it a manual load using the import wizards or is it VBA code using a file format specification or other method? As suggested have you tried skipping excel altogether and loading directly with SAS? EJ ... View more

I probably dont understand the end use case well enough but based on the information in the post it seems like making the data wider will make the problem harder. Appending the data together and specifying a new date variable based on the dataset the information is coming from. This could reduce the number of date prompts as well to just start YR Q and end YR Q. Like I said if Im off base on the use case this may not make sense .... just throwing it out there. EJ ... View more

Its been awhile since I used rsubmit but isnt that only for remote submits to SAS on a server? Which wouldnt be the same as a pass thru query to a different SQL platform. Eric ... View more

Thanks that does seem to work for my example. My goal was to understand this method see it was new to me. Something to file away for future use! 23   proc sql; 24      create table no_dup (drop=_n_) as 25      select * 26      from  (select *,monotonic() as _n_ from duptest) 27      group by name,date 28      having _n_ = min(_n_) 29      order by name,date 30   ; NOTE: The query requires remerging summary statistics back with the       original data. NOTE: Table WORK.NO_DUP created, with 11 rows and 5 columns. 31   quit; NOTE: PROCEDURE SQL used (Total process time):       real time           0.02 seconds       cpu time            0.04 seconds 32 33   proc sort data=duptest out=out nodupkeys; by name date; run; NOTE: There were 12 observations read from the data set WORK.DUPTEST. NOTE: 1 observations with duplicate key values were deleted. NOTE: The data set WORK.OUT has 11 observations and 5 variables. NOTE: PROCEDURE SORT used (Total process time):       real time           0.01 seconds       cpu time            0.01 seconds ... View more

So I tried the trick out and must be doing something wrong. I was expecting the same results from a proc sort nodupkey process but didnt get that. What am I missing? The made up data is attached LOG: 15  proc sql; 16      create table no_dup as 17      select * 18      from duptest 19      group by name, date 20      having monotonic()=min(monotonic()); NOTE: The query requires remerging summary statistics back with the       original data. NOTE: Table WORK.NO_DUP created, with 1 rows and 5 columns. 21  quit; NOTE: PROCEDURE SQL used (Total process time):       real time          0.01 seconds       cpu time            0.01 seconds 22  proc sort data=duptest out=out nodupkeys; by name date; run; NOTE: There were 12 observations read from the data set WORK.DUPTEST. NOTE: 1 observations with duplicate key values were deleted. NOTE: The data set WORK.OUT has 11 observations and 5 variables. NOTE: PROCEDURE SORT used (Total process time):       real time          0.00 seconds       cpu time            0.00 seconds ... View more

Very interesting ... I will have to file this trick away! EJ ... View more

I dont think there is an easy way to do this if you truly want to multiple the two matrices in your example using base SAS (if you have IML then it becomes easier). I would check this article out to see if it helps you. http://www.lexjansen.com/pharmasug/2010/cc/cc15.pdf EJ ... View more

So would it be fair to say you want to split the comment by punctuation? Your could do something like the following: data want;   length comment_1 - comment_7 $ 100;   set have;   comment_1 = scan(comment,1,".","RP");   comment_2 = scan(comment,2,".","RP");   comment_3 = scan(comment,3,".","RP");   comment_4 = scan(comment,4,".","RP");   comment_5 = scan(comment,5,".","RP");   comment_6 = scan(comment,6,".","RP");   comment_7 = scan(comment,7,".","RP"); run; Code could be condensed with an arrary and do loop but I left it alone as to not confuse matters. The R and P are optional modifiers that trim leading and trailing spaces from the result (R) and use all punctuation as delimiters (P). EJ ... View more

Even taking the min of just the date may not get rid of all the duplicates if the min is in the table multiple time for acctno and name and other fields changing... if the values dont really matter then proc sort with out= option is the best choice as mentioned in his post. EJ ... View more

This seems to work with your example assuming that the date is numeric: proc sql;   create table want as   select acct_no, name, min(date) as date   from have   group by acct_no, name   ; quit; EJ ... View more

Are trying to update the table in place or just get a sas dataset to work with? EJ ... View more

That doesnt actually get what they ask for a couple of reasons: - doesnt use SQL - it really is only a no dup on acct_no since the dates are actually unique ... View more