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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.