I have a data set that looks like this:
id | dm_userid | faculty_name_1 | faculty_name_2 | faculty_name_3 | faculty_name_4 | faculty_name_5 | faculty_name_6 | faculty_name_7 | faculty_name_8 | faculty_name_9 | faculty_name_10 | faculty_name_11 | faculty_name_12 | faculty_name_13 | faculty_name_14 | faculty_name_15 |
2443 | 2081502 | 2081502 | 2158644 | 2158656 | 2158660 | |||||||||||
2628 | 2081502 | 2158641 | 2158659 |
And I would like it to look like:
id | dm_userid | faculty_name_1 | faculty_name_2 | faculty_name_3 | faculty_name_4 | faculty_name_5 | faculty_name_6 | faculty_name_7 | faculty_name_8 | faculty_name_9 | faculty_name_10 | faculty_name_11 | faculty_name_12 | faculty_name_13 | faculty_name_14 | faculty_name_15 |
2443 | 2081502 | 2081502 | 2158644 | 2158656 | 2158660 | |||||||||||
2628 | 2081502 | 2158641 | 2158659 | 2081502 |
So basically I need to copy in dm_userid to the various faculty_name_1 - faculty_name_15 fields if dm_userid doesn't exist. But I only need it copied in once, to the first blank field.
The first ID row isn't copied in because that particular dm_userid value already exists in its faculty_name_1 - faculty_name_15. The second ID is copied in because the dm_userid value doesn't already exist.
There will only ever be one dm_userid value per ID.
Any ideas?
Thanks in advance!
Hello,
data want;
set have;
array fac faculty_name:;
if dm_userid not in fac then do over fac;
if fac=. then do;
fac=dm_userid;
leave;
end;
end;
run;
HI @SasPerson85 It appears you want to plug in userid to the first blank faculty_name if the userid doesn't exist in the faculty_name series. Am i right?
Also, what if userid isn't found in the faculty_name series however there is no blank value in the faculty_name series. Or in other words, faculty_name series have values.
data have;
call streaminit(7);
infile cards truncover;
input id dm_userid faculty_name_1 faculty_name_2 faculty_name_3 faculty_name_4 faculty_name_5 faculty_name_6 faculty_name_7 faculty_name_8 faculty_name_9 faculty_name_10 faculty_name_11 faculty_name_12 faculty_name_13 faculty_name_14 faculty_name_15;
array t faculty_name_:;
output;
if _n_=2 then do;
id=ceil(rand('uniform',1,1e6));
do over t;
t=ceil(rand('uniform',1,1e6));
end;
output;
end;
cards;
2443 2081502 2081502 2158644 2158656 2158660
2628 2081502 2158641 2158659
;
data want;
set have;
call missing(faculty_name_16);
array t(*) faculty_name_:;
k=N(of t(*));
if dm_userid not in t then t(k+1)=dm_userid;
drop k;
run;
Hello,
data want;
set have;
array fac faculty_name:;
if dm_userid not in fac then do over fac;
if fac=. then do;
fac=dm_userid;
leave;
end;
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.