I don't think it can be done within a single create table statement. Maybe with SAS 9.4 in a proc DS2 using a SQL select syntax for your set statement. In the mean time, you need your monotonic() function to be applied after your data is ordered but proc sql limits the order by statement to the outer query and proceeds it after everything else effectively incompatible to the desired result. If you randomly happen to have an oracle server, there is apparently an automated ROWNUM variable similar to that of _N_ which might enable you to achieve the results, or not. This was just stuff I read earlier today trying to find ways around the limitation of order by as the final step of the outer query. Fugue, your code will generate an error. In order for the monotonic() as id variable to be created, the monotonic() function has to be called somewhere else. You would minimally need to do where calculated id<5 or where monotonic()<5. It is probably one of the reasons why it is an undocumented function, it behaves oddly. Sadly, it wouldn't achieve the OPs' desired result since he needs the data to be sorted by count desc before the where statement applies as discussed in a reply. ... View more

Tyvm for the effort Tom, I did expect full volume to have issues. Alternation constructs are brutal on NFA PRX efficiency. Nonetheless, I am glad to see the results. I wish SAS supported a "DFA" option for perl. Vincent ... View more

You can go around this issue if you have some knowledge of system commands using call system. I'm not familiar enough with it to specify for a single file but here is an example of a code a colleague made to issue a command that reads all files/folders in a directory and writes it to a temporary file (which he reads in a subsequent step to retrieve the relevant information). data _null_; call system("dir /s /q /T:c /4 /-c " "c:\dirtoread" " >c:\dirtowrite\filetowrite.txt" ); run; Here is an example of a single line output in filetowrite.txt (starting on line 5) 28/05/2013  10:17 AM                    4049 STATCAN\martvin           Exploration.sas This should allow you to get file size (4049), creation date  (28/05/2013 - /T:c option for creation date, /T:w option for last time written) and user who created it (STATCAN\martvin) Edits: *you may need to add options noxwait; before the data step. This could also be done in a X command line instead of call system so as to avoid the data _null_; run; syntax. *Decent source to help you target your file better (xcommands basics) Dir - list files and folders | SS64.com *You can actually get the information only for a file by using full file extension instead of just its root folder. The produced file is still the x command log file so the dataline starts on line 5 but that's easy to go around with INFILE statement options property. After some iterations here's a way to get command log relevant line into your sas log: options noxwait; X "dir /s /q /T:c /4 /-c " "c:\dirtoread" " >c:\temp.txt"; data _null_;      infile 'c:\temp.txt' pad encoding=pcoem850 lrecl=200 firstobs=6 obs=6;      input @1 fileinformation $200.;      put fileinformation; run; Vincent ... View more

I'm not sure to follow or rather, the way I understand the data in the original post, I don't think each records are presenting matches of a pair vs another pair. It reads to me more like a "race against the clock" by pair and then each pair is ranked based on the time they put up. Maybe I really misinterpreted the data but given that there are 8 distinct names or competitors and thus sum_(i=1)^(n-1) i = n*(n+1)/2 = 36 distinct pairs and that for each round robin, there are 36 records minus missings, this truly reads to me as though each round results are ordered by best pair performance to worst pair performance similar to the race against the clock concept. I can't seem to think of any team v team based competition in a round robin setup of all distinct pairs matchup that could result in 36 single matchup winners. If you considered a round robin of matches with a single winer on a multinom(8,2,2,2,2) scenario, you'd have to have  7*6*5*4*3=2520 matches or 1/3 of that if the records represented the winner of a round robin within a round robin to effectively give even chances to each competitor of facing every other possible team configuration within a given team. Anyway I'll watch the thread closely I'm quite intrigued by the concept Vincent ... View more

Hi Nicholas, I just posted a hash table solution that could accomodate your need, at least if I understand properly, in the following thread https://communities.sas.com/message/176069#176069 Obviously, some tweaks would need to be made. data _null_;      if _n_=1 then do;      declare hash h();      h.defineKey('name');      h.defineData('name', 'points');      h.defineDone;      end;      set have;      array round {*} round_1-round_7; /* This effectively allows us to use loops on the 7 rounds rather than hard code them */      do i=1 to 7;           if trim(round{i}) NE "*" then do;           win1 = scan(round{i}, 1, "_");           win2 = scan(round{i}, 2, "_");           roundpoints = max(0, 10-order+1);           rc=h.find(name: win1);           if rc NE 0 then do;                name=win1;                points=roundpoints;                h.add();           end; else do;                points=points+roundpoints;                h.replace();           end;           rc=h.find(name: win2);           if rc NE 0 then do;                name=win2;                points=roundpoints;                h.add();           end; else do;                points=points+roundpoints;                h.replace();           end;           end;      end;        rc=h.output(dataset: "work.want");      drop rc win1 win2 points; run; Here, you are using a hash object to add a new member each time a new name is encountered in the dataset initiating his "wins" value to 1. Afterwards, each time the same name shows in the DS, the 'wins' value is incremented by 1. The resulting data is stored in the hash object hence, the "want" data is output via the hash output method. This will create a dataset with columns 'name' and 'wins'. It is currently untested but the logic and syntax element is all there. Vincent *Edit* Reading Astounding's comment and going through the post you've linked, I believe I had misinterpreted your data. I believe the biggest question which we can't answer for you is how do you measure performance in each round. For instance, you could consider the  sum of all orders for a given individual to be the best indicator of his performances (ascending). Or, similar to, say, F1 standings, you can attribute a score value per position and set everything below a certain position to 0 and then take the total score per round or the best score per round per individual etc. For the sake of providing a better code sample, I've updated the above code to output a dataset that "scores" each round as follow: Top 10 positions gives 10/9.../1 points resp. The sum of all points on all rounds gives the score of an individual. You can then simply sort the Want dataset by descending order to rank players from best to worst. ... View more

Hi Carlos, As pointed out by Ballard, if I were you, I'd use one of the text-distance metrics. However, since you've mentioned sometimes its word separators and the like that are causing inexact match, I would do so on a pre processed "compressed" string (removing spaces, hyphens, dots, commas, etc. basically any special character). The issue with a discrimination rule with just those distances are word ordering ("mouvement desjardins" and "desjardins mouvement" would be extremely far appart). There are other text distance metrics although I am not aware that they are built-in SAS. Another alternative is to try to minimize character transitions with a 27*27 matrix of transition for each string. Here is a simplified version of something I had done a while back. I had build a FCMP function to compare a single variable, for each record, against an entire Levenstein dictionary (complev function). I'm not providing anything with regards to proc FCMP but rather how you can use _TEMPORARY_ arrays as a dictionary when dictionary size is not too big. /* steps to put the number of records in your accounts dataset into macro variable dbsize*/ data want;     if _N_=1 then do;                 array accountname{&dbsize} $80. _temporary_;                 array accountid{&dbsize} $3. _temporary_;                 do i = 1 to &dbsize;                         set database;                         accountname{i} =                          accountid{i} = accountid;                 end; /* read your masters dataset accountname/id into temporary arrays that are retained in memory through the entire data step */         end;      set have; /* loop on dataset have or, in your case, the lookup excel sheet */      bestmatchid=""; bestmatchdistance=80;      do i=1 to &dbsize;            temp=complev(compress(accountname{i}, " -;:,.\/#'*&?!$@"), compress(account_name, " -;:,.\/#'*&?!$@"), "nl");             if temp<bestmatchdistance then bestmatchid=accountid{i};       end;      drop bestmatchdistance temp; run; for testing you may want to remove the drop statement~ Basically what this does is run every single account_name in your lookup dataset against a "dictionary" of account names & id pairs preformatting the data. Note that the complev modifier n ignores case hence I did not add an upcase or lowcase function to the compress. There's currently no case handling if multiple matches are found or if the distance is too small or even if the best distance is way too big. Levenstein computes the number of transformation to go from string A to string B comp GED might suit your need more or you may even need a custom string distance function but the above concept of using temporary arrays to read your masters data only once still holds and can be quite useful. Significantly more than doing a cartesian product of the 2 DS and run a distance function on each such record. Vincent ... View more

Odd. Here's my log on a fresh new session SAS 9.2 under windows 32-bit ------------------------------------------------- 1    %macro test (name=); 2       data _null_; 3       call symputx ('name', "&name", 'Global'); 4       run; 5       %put &name; 6    %mend test; 7    %test (name=Vince); ERROR: Attempt to %GLOBAL a name (NAME) which exists in a local environment. NOTE: Argument 1 to function SYMPUTX at line 1 column 23 is invalid. _ERROR_=1 _N_=1 NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time):       real time           0.51 seconds       cpu time            0.00 seconds Vince ------------------------------------------------- Would you mind testing the following code segment %symdel name1; %macro test (name=);    data _null_;    call symputx ('name1', "&name"||"2", 'Global');    run;    %put &name1; %mend test; %test (name=Vince); I assume somehow either in 9.3 or under linux, the macro processor behaves slightly differently. If it returns something like WARNING: Apparent symbolic reference NAME1 not resolved. I would guess that under linux, the macro processor does not attempt to execute part of the macro and thus the new global macro variable only exists once the macro was exited and that you did not get an error with the above example because it used the local macro variable name. If however it does resolve to Vince2, then I guess between 9.2 and 9.3 they improved the macro processor on such ambiguities. If the former case applies, then it still means that using the call symputx syntax does not allow you to use the new global macro content within your macro whereas some of the alternatives I've examplified above would. Thanks, Vincent ... View more

Indeed, there are a whole lot of ways around and each with their own advantages and disadvantages. For instance, if you attempt to do %put &name; instead of %put _user_;, you will get an error because the macro processor recognizes that macro variable name was updated through the macro and thus segments the macro code, executes the data _null_; ... run; segment to update the macro variable only to find out that the call symputx cannot be processed while within the macro as you are trying to declare a %global name; in an environment where %local name; already exists. %macro test (name=);    data _null_;    call symputx ('name', "&name", 'Global');    run;    %put &name; %mend test; %test (name=Vince); There are other alternatives as well like passing the name of a global macro variable or  declaring a bunch of different %global inside the macro and documenting the macro that it will replace any such existing macro values if they already exist in the global environment. E.g. %let name=somelib.myds; %let nameout=placeholder; /* statement not necessary, I just meant to depict that you can declare a variable as global from within a macro even when it was already declared as such. */ %macro t(namein=); %global nameout; %let nameout=work.%scan(&namein, 2); data &nameout; x=1; run; %mend; %t(namein=&name); %put &nameout; /* acts as a macro output parameter */ I'm still learning some of the twists of functioning with macros especially in terms of best practice but for some reason, I've always tried to stay away from using call symputx within a macro unless it was specifically to read data from a dataset as needed locally by the macro...and I think the first example explains why. However, I am more than welcome to read from more experienced users about this particular topic. I typically prefer passing the name of a macro variable in the global scope if I want my macros arguments to be meaningful. %let name=work.myds; %macro t(namein=); data &&&namein; /* Since I know that I am passing a macro variable name, in order to resolve its value I need too (&&) (&namein) resolved to -> (&) (name) -> work.myds; */ x=1; run; %mend; %t(namein=name); However, it can get confusing. SAS macro language is actually a very fun brain tease since it is basically just a mass text parser and mimicing programming language functionalities like C sometimes requires a twist of mind. Anyway, Cheers! Vincent ... View more