Help using Base SAS procedures

Creating usernames and passwords (concatenate and substrings)?

Accepted Solution Solved
Reply
Super Contributor
Posts: 276
Accepted Solution

Creating usernames and passwords (concatenate and substrings)?

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.


Accepted Solutions
Solution
‎03-13-2015 12:54 PM
Super User
Super User
Posts: 6,708

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

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;

View solution in original post


All Replies
Super User
Posts: 10,880

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

What have you attempted so far?

Super Contributor
Posts: 276

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

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.

Super User
Posts: 10,880

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

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.

Super Contributor
Posts: 276

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

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

Super User
Posts: 10,880

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

Use COMPRESS on the full last name before concatenation.

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

Super Contributor
Posts: 276

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

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'));

Solution
‎03-13-2015 12:54 PM
Super User
Super User
Posts: 6,708

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

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;

Super Contributor
Posts: 276

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

Thanks much!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 299 views
  • 3 likes
  • 3 in conversation