BookmarkSubscribeRSS Feed
Mentu
Calcite | Level 5
I have this data    
      
Country Monthyear Apples Oranges Mango 
USAJan2018030
USAJan2018110
USAFeb2018101
USAFeb2018022
USAFeb2018001

 

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 Monthyear Fruit typeNumber       
USAJan2018Apples0       
USAJan2018Apples1       
USAFeb2018Apples1       
USAFeb2018Apples0       
USAFeb2018Apples0       
USAJan2018Oranges3       
USAJan2018Oranges1       
USAFeb2018Oranges0       
USAFeb2018Oranges2       
USAFeb2018Oranges0       
USAJan2018Mangos0       
USAJan2018Mangos0       
USAFeb2018Mangos1       
USAFeb2018Mangos2       
USAFeb2018Mangos1       
3 REPLIES 3
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
Ksharp
Super User

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 910 views
  • 0 likes
  • 3 in conversation