topic Re: Creating usernames and passwords (concatenate and substrings)? in SAS Procedures

Creating usernames and passwords (concatenate and substrings)?

GreggB — Fri, 13 Mar 2015 15:11:19 GMT

I want a user name and password for each record. The data looks like so:

schoolid last first middle studentid

5 Jagger Mick Ron 45678

Username for this record would 5mrjagger >>> schoolid || first initial || middle initial || lastname

(all letters in lower case. some names have hyphens and apostrophes so I need those stripped out)

Password for this record would mrj5678 >>> first initial || middle initial || last initial || last 4 digits of studentid

(Some studentids are longer than 5 characters so I need to start at the end and count back 4 places. All studentids and schoolids are numeric)

I want to make sure there are no leading or trailing spaces in the usernames and passwords.

Thanks for any help you can provide.

Re: Creating usernames and passwords (concatenate and substrings)?

ballardw — Fri, 13 Mar 2015 15:38:09 GMT

What have you attempted so far?

Re: Creating usernames and passwords (concatenate and substrings)?

GreggB — Fri, 13 Mar 2015 15:43:31 GMT

I have this: username=schoolid||lowcase(compress(substr(first,1,1),'s'))||lowcase(compress(last,'s'));

I thought the 's' would eliminate spaces but it doesn't seem to. (I just realized I don't need it for first names.

Re: Creating usernames and passwords (concatenate and substrings)?

ballardw — Fri, 13 Mar 2015 15:51:16 GMT

You might want to look at the CATS function instead of || operator.

username=CATS(schoolid, lowcase(substr(first,1,1)), lowcase(last); As a starting point.

since you have a length limit you will likely want to test the LENGTH of cats(schooled,substring(first,1,1)) to see how many characters to use from LAST.

It may be easier to use userid = lowcase( <expression) to avoid repeated to calls to lowcase in the concatenation.

Re: Creating usernames and passwords (concatenate and substrings)?

GreggB — Fri, 13 Mar 2015 15:56:21 GMT

Yes, this works. Now, I need to get rid blanks, hyphens, etc for last names such as O'hara, A-Rod and sara-jane.

Re: Creating usernames and passwords (concatenate and substrings)?

ballardw — Fri, 13 Mar 2015 16:06:03 GMT

Use COMPRESS on the full last name before concatenation.

possibly last=compress(last,'lik');

Re: Creating usernames and passwords (concatenate and substrings)?

GreggB — Fri, 13 Mar 2015 16:09:28 GMT

Here's what I have now. It got rid of spaces and punctuation.

username=schoolid||compress(compress(lowcase(substr(first,1,1))||lowcase(substr(middle,1,1))||lowcase(last),,'p'));

Re: Creating usernames and passwords (concatenate and substrings)?

Tom — Fri, 13 Mar 2015 16:54:28 GMT

You probably also need to watch out for duplicates.

data have ;

length schoolid 8 last first middle $30 studentid $10 ;

input schoolid -- studentid ;

cards;

5 Jagger Mick Ron 45678

5 Jagger Mike Randle 876

4 O'Hara Kathrine . 12345

;;;;

data want ;

set have ;

length username $20 password $20 ;

username=lowcase(cats

(schoolid

,substr(first,1,1)

,substr(middle,1,1)

,compress(last,,'kn')

));

password=lowcase(cats

(substr(first,1,1)

,substr(middle,1,1)

,substr(compress(last,,'kn'),1,1)

,substr(studentid,max(1,length(studentid)-3))

));

run;

proc sort;

by username ;

run;

data want ;

set want ;

by username ;

if first.username then n=0;

n+1;

if not (first.username and last.username) then username=cats(username,n) ;

put username= password=;

run;

Re: Creating usernames and passwords (concatenate and substrings)?

GreggB — Fri, 13 Mar 2015 17:11:25 GMT

Thanks much!