Hi, I am looking for help or some guidance to automate my simple code using macro variables and possible the best method a macro. I am doing some basic research and would like to cut out the manual element of my sas code. The below is all test data I have just created. The actual dataset layout I have is a carbon copy of the below. The way the data is stored for preffered sports is not ideal but what has been set. I am looking to find the number of people and total value who likes a specific combination of sports. The combination should be based off the table 'interest. E.g from the list, football and basketball, football and darts, darts and basketball. This is then to be used in the code which I have named as 'summary'. Rather than manually entering the different combinations, is there a way for a macro to do this work for me? My actual dataset will contain more than 3 variables to be used for the combination but I have limited this to 3 in the test example below. Thanks Jacko data research;
length name $10 Value 8. Preferred_Sports $100;
infile datalines dsd;
input Name $ Value Preferred_Sports $;
datalines;
Jude,32,"Football,Basketball,Tennis,Darts"
Jack,39,"Hockey,Darts,Judo"
Harry,20,"Basketball,Tennis,Hockey"
Tom,8,"Badminton,Tennis,Darts"
Boris,14,"Badminton,Hockey,Judo"
Pier,18,"Judo"
Michael,12,"Darts,Polo,Tennis"
Roger,49,"Hockey,Darts,Judo"
Felix,1,"Football,Basketball,Tennis,Darts"
;
data interest;
infile datalines dsd;
input Sport $;
datalines;
Football
Basketball
Darts
;
proc sql;
create table summary as
select
count (distinct name) as people,
sum (value) as total_value
from research
where Preferred_Sports contains 'Football'
and Preferred_Sports contains 'Basketball'
;quit;
want:
Football = &Football
Basketball = &Basketball
Darts = &Darts
automation so that the below code will go through the different options above. e.g. where preffered sports contains football and preffered sport contain Basketball, where preffered sports contains football and preffered sport contain darts, where preffered sports contains basketball and preffered sport contain darts etc
proc sql;
create table summary as
select
count (distinct name) as people,
sum (value) as total_value
from research
where Preferred_Sports contains '&Football'
and Preferred_Sports contains '&Basketball'
;quit;
... View more