topic Re: Combining multiple columns into 1 in SAS Programming

Combining multiple columns into 1

Mentu — Thu, 04 Apr 2019 13:22:20 GMT

I have this data

Country	Month	year	Apples	Oranges	Mango
USA	Jan	2018	0	3	0
USA	Jan	2018	1	1	0
USA	Feb	2018	1	0	1
USA	Feb	2018	0	2	2
USA	Feb	2018	0	0	1

I want to to combine the fruit columns into 1 separate 'Fruit' column and their corresponding frequency. How do i do this with SAS base?
Country	Month	year	Fruit type	Number
USA	Jan	2018	Apples	0
USA	Jan	2018	Apples	1
USA	Feb	2018	Apples	1
USA	Feb	2018	Apples	0
USA	Feb	2018	Apples	0
USA	Jan	2018	Oranges	3
USA	Jan	2018	Oranges	1
USA	Feb	2018	Oranges	0
USA	Feb	2018	Oranges	2
USA	Feb	2018	Oranges	0
USA	Jan	2018	Mangos	0
USA	Jan	2018	Mangos	0
USA	Feb	2018	Mangos	1
USA	Feb	2018	Mangos	2
USA	Feb	2018	Mangos	1

Re: Combining multiple columns into 1

Jagadishkatam — Fri, 05 Apr 2019 08:49:19 GMT

Please try the below code

data have;
input Country:$3. 	Month:$3.	year 	Apples 	Oranges 	Mango ;
cards;
USA Jan 2018 0 3 0
USA Jan 2018 1 1 0
USA Feb 2018 1 0 1
USA Feb 2018 0 2 2
USA Feb 2018 0 0 1
;

data want;
set have;
array test(3) Apples 	Oranges 	Mango;
do i = 1 to 3;
fruit=vname(test(i));
number=test(i);
output;
end;
drop Apples 	Oranges 	Mango i;
run;

Re: Combining multiple columns into 1

Jagadishkatam — Fri, 05 Apr 2019 08:59:20 GMT

by proc transpose

proc sort data = have;
by country month year ;
run;

proc transpose data=have out=trans(rename=(_name_=fruit));
by country month year ;
var Apples 	Oranges 	Mango ;
run;

proc sort data = trans;
by country month year fruit;
run;

options missing=0;
proc transpose data=trans out=trans2;
by country month year fruit;
var col:;
run;

Re: Combining multiple columns into 1

Ksharp — Fri, 05 Apr 2019 13:34:18 GMT

If you don't have many variables, try this one,otherwise try PROC TRANSPOSE .

data have;
input Country:$3. 	Month:$3.	year 	Apples 	Oranges 	Mango ;
cards;
USA Jan 2018 0 3 0
USA Jan 2018 1 1 0
USA Feb 2018 1 0 1
USA Feb 2018 0 2 2
USA Feb 2018 0 0 1
;
data want;
 set have(keep= Country 	Month	year 	Apples rename=(Apples=number) in=ina)
 have(keep= Country 	Month	year 	Oranges rename=(Oranges=number) in=inb)
 have(keep= Country 	Month	year 	Mango rename=(Mango=number) in=inc);
length type $ 80;
 if ina then type='Apples';
 if inb then type='Oranges';
 if inc then type='Mango';
run;