Hi All, I've created the following macro to generate random IDs/passwords. It essentially constructs a data step with a bunch of output statements. It allows the user to specify the allowed characters, number of passwords and password length. Also included is a check to ensure duplicate IDs/passwords are not included. My question: Is there a more efficient way to accomplish this? Tweaks to my code or a fundamentally different approach? This macro bogs down when asked to generate 10000 or more IDs/passwords. I originally tried to generate a DATALINES statement with the macro (not allowed) and also tried PROC SQL INSERT, which works but isn't any faster. *characters to place in the ID, space delimited; %let allowed = 1 2 3 4 5 6 7 8 9 a b c d e f h i j k m n p r s t u v w x y z; %let numIDs = 100;    *number of IDs to create; %let idLen = 6;            *number of characters per ID; %macro ran6;      /*delimeters for concatenating macro variables*/      %let delim_nospace = %str();      %let delim_space = %str( );      %let numChars = %length(%sysfunc(compress(&allowed,' '))); *num of total characters available;      *%put numchars: &numChars;      %let used = ; *generate IDs go here to check for duplicates;         *output dataset;   data id_output;   call streaminit(23);  *set random seed;   length userID $ &idLen.;      *this loop controls the number of IDs to generate;      %do i = 1 %to &numIDs;      *this loop controls the length of an ID;      %do j = 1 %to &idLen;           *follow http://blogs.sas.com/content/iml/2011/08/24/how-to-generate-random-numbers-in-sas/;           %let u = %sysfunc( rand(UNIFORM) ); *u is random decimal;           /*sysevalf works because https://communities.sas.com/message/51523#51523*/           %let x = %sysevalf(1 + (&numChars - 1 ) * &u.);            *set random into with upper bound eq to number of characters;           %let k = %sysevalf(&numChars * &u , ceil) ;            *pull a letter fro the allowed list;           %let IDPart = %scan(&allowed.,&k, ' ');           /*%put u: &u  x: &x k: &k IDPart: &IDPart;*/           %if &j = 1 %then %let userID = &IDPart;           /*concatenate macro variables, see https://communities.sas.com/message/180272 */           %else %let userID = %sysfunc(catx(&delim_nospace.,%nrstr(%superq(userID)),%nrstr(%superq(IDPart))));      %end;               /*if duplicate found, do not increment i*/      %if %index(&used, &userID) %then %let i = %eval(&i -1);      /*no duplicat found, write output statement*/      %else %do;            userID = "&userID";            output;      %end;      /*add mewest ID to macro variable for duplicate checking*/      %let used = %sysfunc(catx(&delim_space,%nrstr(%superq(used)),%nrstr(%superq(userID))));      /*%put used: &used;*/      /*%put UserID: &userID;*/      %end; run; %mend; %ran6; ... View more

I'm stumped and could use some help coding this logic. I essentially need to do a conditional find and replace across the entire dataset. I've tried using hash tables but the code and output inevitably confuse so I am leaving it out for now. I will add the hash code if requested. The setup: /* Rules: IF Study = A and Flag = 1 then Can_Link = 1 for all instances of associated MID IF Study = B and Flag = 1 then Can_Link = 1 where Study = B or E for all instances of associated MID Else Can_Link = Flag */ data have; input MID $ RID $ Flag Study $ Can_Link ; datalines;                      1 6 1 A . 1 5 0 B . 2 3 0 A . 2 4 1 A . 2 1 0 B . 3 8 0 C . 4 7 1 A . 5 9 0 D . 6 10 0 E . 6 11 1 B . 6 12 0 C . ; run; *output should match this; data want; input MID $ RID $ Flag Study $ can_link ; datalines;                      1 6 1 A 1 1 5 0 B 1 2 3 0 A 1 2 4 1 A 1 2 1 0 B 1 3 8 0 C 0 4 7 1 A 1 5 9 0 D 0 6 10 0 E 1 6 11 1 B 1 6 12 0 C 0 ; run; Edit to fix error pointed out by Tom ... View more

Hi All, I would like to count the maximum number of value columns with the same value across any number of columns in the same row. For a small number of variables, this can be done with a bunch of conditional statements. However, this approach quickly falls apart as the number of variables increases. Is there a more elegant, dynamic approach?  To illustrate: data have; input col1 $ col2 $ col3 $ col4 $; cards; a b b b a b b d c b c b d d d d a d a a ; data want;      set have;      if col1 = col2 = col3 = col4 then count = 4;      else if col1 = col2 = col3   then count = 3;      else if col1 = col2 = col4       then count = 3;      else if col1 = col3 = col4   then count = 3;      else if col2 = col3 = col4   then count = 3;      else if col2 = col3 = col4   then count = 3;      else if col1 = col2        then count = 2;      else if col1 = col3        then count = 2;      else if col1 = col4        then count = 2;      else if col2 = col3        then count = 2;      else if col2 = col4        then count = 2;      else if col3 = col4        then count = 2;      else count = 1; run; I found this code from  Messr. Tabachneck to be close but it counts the number of matching pairs rather than the number of columns with the same value. data want (drop=i j); set have; array vars{*} col1-col4; count=0;   do i=1 to dim(vars)-1;      do j=i+1 to dim(vars);     count=sum(count, vars(i) eq vars(j));      end;   end; run; Best, kbk ... View more

Hi All, I have a number of datasets with common and unique variables. I want to create a master list of all the variables and add columns that indicate which datasets have which variables. I figured out a way to do this with proc sql and an outer join (see below) but it only really works with 2 tables. It appears a match-merge approach with a datastep is the way to go but I'm not sure how to add the checklist columns. Any suggestions? Here is what I have so far: data work.table1;     input name $ desc $;     datalines; a descA b descB c descC d descD run; data work.table2;     input name $ desc $;     datalines; b descB d descD e descE f descF run; data work.table3;     input name $ desc $;     datalines; b descB c descC f descF g descG h descH run; * this produces what I want but only works for 2 tables; * merge the files and add a checklist column for tables 1 and 2; proc sql;     CREATE TABLE checkListSQL AS     SELECT         CASE             WHEN a.name = b.name THEN a.name             WHEN a.name = '' THEN b.name             WHEN b.name = '' THEN a.name             ELSE 'NULL'             END AS Variable length = 10,         CASE             WHEN a.name ~= '' then a.Desc             ELSE b.DESC             END as Description length = 50,         CASE             WHEN a.name = b.name THEN 'X'             WHEN a.name ~= '' THEN 'X'             ELSE ''             END as table1 length = 10,         CASE             WHEN b.name = a.name THEN 'X'             WHEN b.name ~= '' THEN 'X'             ELSE ''             END as table2 length =10     FROM table1 as a     FULL JOIN table2 as b     ON a.name = b.name; quit; data checkListMerge;     merge table1 table2 table3;     by name; *data already sorted;     *How to add the checklist portion via a data step?; run; ... View more