topic Re: Inserting value into rows in SAS Programming

Inserting value into rows

saweheh — Wed, 04 Aug 2021 10:26:18 GMT

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

Re: Inserting value into rows

Kurt_Bremser — Wed, 04 Aug 2021 10:47:51 GMT

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.

Re: Inserting value into rows

saweheh — Wed, 04 Aug 2021 11:38:26 GMT

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.

Re: Inserting value into rows

Kurt_Bremser — Wed, 04 Aug 2021 11:55:02 GMT

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.

Re: Inserting value into rows

Ksharp — Wed, 04 Aug 2021 12:05:47 GMT

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;

Re: Inserting value into rows

saweheh — Wed, 04 Aug 2021 23:43:59 GMT

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

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

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

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

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

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

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