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;

SAS Innovate 2025: Register Now

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!

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
  • 651 views
  • 0 likes
  • 3 in conversation