Retun multiple value for a single value lookup

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Retun multiple value for a single value lookup

Hi

I have a two data sets on one data set there is data like this

Data Have 

AA 1  

BB   18

AA    2

AA   1

data want

AA 1 , 2 , 1

BB 18,

please note that I don't want to merge and then do a transpose because the headers will be same In my case and the above given table the name 'AA' will be repeated.

Thanks


Accepted Solutions
Solution
‎07-22-2015 09:07 AM
Super User
Posts: 9,856

Re: Retun multiple value for a single value lookup

Code: Program

data Have ;
input id $ v;
cards;
AA 1 
BB 18
AA 2
AA 1
;
run;
proc sort data=have ;by id;run;
data want;
set have;
by id;
length value $ 200;
retain value;
value=catx(',',value,v);
if last.id then do;output;call missing(value);end;
drop v;
run;

View solution in original post


All Replies
Solution
‎07-22-2015 09:07 AM
Super User
Posts: 9,856

Re: Retun multiple value for a single value lookup

Code: Program

data Have ;
input id $ v;
cards;
AA 1 
BB 18
AA 2
AA 1
;
run;
proc sort data=have ;by id;run;
data want;
set have;
by id;
length value $ 200;
retain value;
value=catx(',',value,v);
if last.id then do;output;call missing(value);end;
drop v;
run;

Contributor
Posts: 31

Re: Retun multiple value for a single value lookup

However this code will have a problem when the numbers exceed the column  length which is 200 in this case is there a way that the numbers or characters which ever the result may be can be put to different columns instead of one column

Thanks

Super User
Posts: 9,856

Re: Retun multiple value for a single value lookup

You mean using variables to store their value ?  PROC TRANSPOSE

Code: Program

data Have ;
input id $ v;
cards;
AA 1 
BB 18
AA 2
AA 1
;
run;
proc sort data=have ;by id;run;
proc transpose data=have out=want(drop=_name_);
by id;
var v;
run;

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 256 views
  • 1 like
  • 2 in conversation