BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreggB
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
ballardw
Super User

What have you attempted so far?

GreggB
Pyrite | Level 9

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.

ballardw
Super User

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.

GreggB
Pyrite | Level 9

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

ballardw
Super User

Use COMPRESS on the full last name before concatenation.

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

GreggB
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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