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;

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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