Duplicate values display only once

[mmkr]

How i do i remove duplicates and display only once i proc report ?

Please see the input and output

Input1

Col1

Col2

Col3

Col4

Col5

Input2

Col1

Col2

Col3

Col4

Col5

laptop

Keyboard

Mouse1

Screen

watch1

laptop

Keyboard

Mouse1

watch1

laptop

Keyboard

Mouse2

Screen

watch2

laptop

Keyboard

Mouse2

watch2

laptop

Mouse3

watch3

laptop

Mouse3

watch3

laptop

laptop

laptop

laptop

Output1

Col1

Col2

Col3

Col4

Col5

Output2

Col1

Col2

Col3

Col4

Col5

laptop

Keyboard

Mouse1

Screen

watch1

laptop

Keyboard

Mouse1

watch1

Mouse2

watch2

Mouse2

watch2

Mouse3

watch3

Mouse3

watch3

[Tom]

Your example datasets do not appear to make any sense as structured.

Why would you want the observation where COL3 = 'Mouse2' to change the value of COL1 from 'laptop' to ' '?  Wouldn't that break the relationship?

If the data is not really intended to be a dataset then restructure it to reflect the independence of the values.

data have;
  input (Col1-Col5) (:$10.);
cards;
laptop  Keyboard  Mouse1  Screen  watch1
laptop  Keyboard  Mouse2  Screen  watch2
laptop  .         Mouse3  .       watch3
laptop  .         .       .       .
laptop  .         .       .       .
;

data tall;
  set have;
  length name $32 value $10;
  array col col1-col5 ;
  do i=1 to dim(col);
    name=vname(col[i]);
    value=col[i];
    if value ne ' ' then output;
  end;
  keep name value;
run;

proc sort data=tall nodupkey;
  by name value;
run;

If for some reason you want to transpose it back into that strange structure then you might want to add a row counter variable.

data for_transpose;
  set tall;
  by name;
  if first.name then row=1;
  else row+1;
run;

proc sort;
  by row name;
run;

proc transpose data=for_transpose out=want(drop=_name_);
  by row;
  id name;
  var value;
run;

[mmkr]

I have ID as unique but above code is not working

Input1	ID	Col1	Col2	Col3	Col4	Col5
	123	laptop	Keyboard	Mouse1	Screen	watch1
	123	laptop	Keyboard	Mouse2	Screen	watch2
	123	laptop		Mouse3		watch3
	123	laptop
	123	laptop
Output1	ID	Col1	Col2	Col3	Col4	Col5
	123	laptop	Keyboard	Mouse1	Screen	watch1
	123			Mouse2		watch2
	123			Mouse3		watch3

[Tom · Accepted solution]

It is much easier to just type the data directly into your program than trying to get in to line up into boxes.

data have;
  input ID (Col1-Col5) (:$10.);
cards;
123 laptop  Keyboard  Mouse1  Screen  watch1
123 laptop  Keyboard  Mouse2  Screen  watch2
123 laptop  .         Mouse3  .       watch3
123 laptop  .         .       .       .
123 laptop  .         .       .       .
;

If you have an ID variable then make sure to include that in your code.

data tall;
  set have;
  length name $32 value $10;
  array col col1-col5 ;
  do i=1 to dim(col);
    name=vname(col[i]);
    value=col[i];
    if value ne ' ' then output;
  end;
  keep id name value;
run;

proc sort data=tall nodupkey;
  by id name value;
run;

data for_transpose;
  set tall;
  by id name;
  if first.name then row=1;
  else row+1;
run;

proc sort;
  by id row name;
run;

proc transpose data=for_transpose out=want(drop=_name_);
  by id row;
  id name;
  var value;
run;

[mmkr]

Thanks so much it worked as i needed !!