creating multiple columns from rows

[jerry898969]

data temp ;
input ID $ val source 8;
datalines;
V1 0 1
V1 10 1
V1 100 2
V1 250 2
V2 50 1
V2 80 1
V2 500 2
V7 10 1
V7 20 1
V7 30 2
V7 99 2
;
run;

I need to create two seperate columns that comma delimit the values. Based on the above table I need the following result.


ID GOOD BAD
V1 '0, 10' '100,250'
V2 '50,80' '500'
V7 '10.20' '30,99'

This is the code I have so far
data temp2 ;
set temp ;
by id ;
retain arr_good ;
length arr_good $ 50;

arr_good=ifc( first.id,val,cats(arr_good,',',val));
if last.id then output;
run;

But this code puts all the rows for each id in one column. I can't figure out how to check for the source to split it up into two separate columns.

Thank You

[Peter_C]

[pre]data temp2 ;
do until( last.id ); * sometimes referred to as a DoW loop ;
set temp ;
by id ;
length arr_good arr_bad $ 50;
if source = 1 then
arr_good=cats(arr_good,',',val) ;
else
arr_bad =cats(arr_bad ,',',val) ;
end ;
* now remove leading commas ;
arr_good=substr(arr_good,2) ;
arr_bad =substr(arr_bad ,2) ;
output;
run;[/pre]

[MaxW]

Here's another option:
data temp ;
input ID $ val $ source 8.;
datalines;
V1 0 1
V1 10 1
V1 100 2
V1 250 2
V2 50 1
V2 80 1
V2 500 2
V7 10 1
V7 20 1
V7 30 2
V7 99 2
;
run;
proc sort data=temp;
by id source;
run;
Data temp2;
retain Arr_good Arr_Bad;
length arr_good $50. arr_bad $50.;
set temp;
by id source;

if first.source and source=1 then Arr_good=val;
else if source=1 then arr_good=cats(arr_good,',',val);

if first.source and source=2 then Arr_bad=val;
else if source=2 then Arr_bad=cats(arr_bad,',',val);

if last.id then output;
drop val source;
run;

[jerry898969]

Thank you both for your replies.

I ended up going with the way MaxW did it because it was closer to where I was at.

Thanks again

[deleted_user]

hello,

you can also use arrays and the catx function for inserting the comma separator:

[pre]
data temp ;
input ID $ val source;
datalines;
V1 0 1
V1 10 1
V1 100 2
V1 250 2
V2 50 1
V2 80 1
V2 500 2
V7 10 1
V7 20 1
V7 30 2
V7 99 2
;
run;

proc sort data=temp;
by id ;
run;

data temp2 ;
set temp ;
by id ;

array build{2} $ 50 arr_good arr_bad;
retain build('');

if first.id then call missing(arr_good,arr_bad);

build{source}=catx(',',build{source},val);

if last.id then output;

drop val source;
run;

[/pre]

Marius

[Peter_C]

Marius
that's very good! Using catx() eliminates the leading delimiter caused by cat(), so eliminates the need to test for first.
Applying that and the array approach greatly simplifies my DoW [pre]data temp2 ;
array arr_d(2) $50 arr_good arr_bad ;
do until( last.id ); * sometimes referred to as a DoW loop ;
set temp ;
by id ;
arr_d(source)=catx( ',', arr_d(source), val ) ;
end ;
run;[/pre]no need to clear the arr_bad and good because no need to retain them, nor is there any need for explicit output because each data step iteration deals with just one ID value.