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 |
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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.