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.
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;
What have you attempted so far?
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.
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.
Yes, this works. Now, I need to get rid blanks, hyphens, etc for last names such as O'hara, A-Rod and sara-jane.
Use COMPRESS on the full last name before concatenation.
possibly last=compress(last,'lik');
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'));
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;
Thanks much!
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.
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.