Copy value to other columns on condition

[SasPerson85]

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!

[novinosrin]

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. 

[SasPerson85]

Yes you are correct in your thinking.

For your second question, if that were to ever happen, then an additional faculty_name_ series field will need to be created and have the dm_userid inputted in the new field.

[novinosrin]

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;

[gamotte · Accepted solution]

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;