topic Re: creating multiple columns from rows in SAS Procedures

creating multiple columns from rows

jerry898969 — Thu, 03 Feb 2011 17:26:17 GMT

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

Re: creating multiple columns from rows

Peter_C — Thu, 03 Feb 2011 17:50:22 GMT

[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]

Re: creating multiple columns from rows

MaxW — Thu, 03 Feb 2011 18:01:47 GMT

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;

Re: creating multiple columns from rows

jerry898969 — Thu, 03 Feb 2011 18:24:40 GMT

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

Re: creating multiple columns from rows

deleted_user — Fri, 04 Feb 2011 09:52:17 GMT

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

Re: creating multiple columns from rows

Peter_C — Fri, 04 Feb 2011 11:40:23 GMT

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.