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 |
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;
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;
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 |
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;
Thanks so much it worked as i needed !!
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.