Inserting value into rows

[saweheh]

Please help. I need to replace "0" with the character per individual ID. From Table 1 to Table 2. Thanks for your help.

Table 1:

ID	M0	M1	M2	M3	M4	M5
1	A	0	C	0	0	0
1	0	0	0	0	0	0
1	0	0	0	0	0	D
2	0	0	D	0	0	C
2	B	0	0	0	0	0
3	C	A	0	0	0	0
3	0	0	0	0	0	A
4	D	0	0	0	0	0
4	0	0	0	0	0	D
4	0	0	0	0	0	0

Table 2

ID	M0	M1	M2	M3	M4	M5
1	A	0	C	0	0	D
1	A	0	C	0	0	D
1	A	0	C	0	0	D
2	B	0	D	0	0	C
2	B	0	D	0	0	C
3	C	A	0	0	0	A
3	C	A	0	0	0	A
4	D	0	0	0	0	D
4	D	0	0	0	0	D
4	D	0	0	0	0	D

[Kurt_Bremser · Accepted solution]

See here:

data have;
infile datalines dlm="09"x;
input (ID M0 M1 M2 M3 M4 M5) ($);
datalines;
1	A	0	C	0	0	0
1	0	0	0	0	0	0
1	0	0	0	0	0	D
2	0	0	D	0	0	C
2	B	0	0	0	0	0
3	C	A	0	0	0	0
3	0	0	0	0	0	A
4	D	0	0	0	0	0
4	0	0	0	0	0	D
4	0	0	0	0	0	0
;

data want;
if 0 then set have; /* retrieves variables */
array m m0-m5;
array _m $ _m0-_m5;
do _i = 1 to dim(_m);
  _m{_i} = "0";
end;
do until (last.id);
  set have;
  by id;
  do _i = 1 to dim(m);
    if m{_i} ne "0" then _m{_i} = m{_i};
  end;
end;
do until (last.id);
  set have;
  by id;
  do _i = 1 to dim(m);
    m{_i} = _m{_i};
  end;
  output;
end;
drop _:;
run;

Note how example data is presented as a data step with datalines, so we can quickly recreate the dataset as is with a simple copy/paste and submit. Please do so yourself in the future, as it speeds up the process of finding a solution.

[saweheh]

Thank you so much. It works perfectly. I'll try to implement this for my project.

Unfortunately I can't use the datalines as you suggested because I'm reading the data from a big table that has thousands of rows and columns. 

[Kurt_Bremser]

The DATALINES concerns how you present example data here. Nobody expects you to post a million-observation dataset in this way, just the example you use for illustration of your issue.

So my first data step is not needed for your code, just replace "have" in the SET statements with the name of your dataset. Make sure that it is sorted properly.

[Ksharp]

data have;
infile datalines expandtabs;
input (ID M0 M1 M2 M3 M4 M5) ($);
datalines;
1	A	0	C	0	0	0
1	0	0	0	0	0	0
1	0	0	0	0	0	D
2	0	0	D	0	0	C
2	B	0	0	0	0	0
3	C	A	0	0	0	0
3	0	0	0	0	0	A
4	D	0	0	0	0	0
4	0	0	0	0	0	D
4	0	0	0	0	0	0
;
proc sql;
create table want(drop=dummy) as
select id,max(m0) as m0,max(m1) as m1,
max(m2) as m2,max(m3) as m3,max(m4) as m4,max(m5) as m5,m5 as dummy
 from have
  group by id;
quit;

[saweheh]

Wow. I'm so surprised with your few lines of code. It worked too. Thanks heaps.