Recurring frequency tables

[spg]

I’m new to the macro facility and was trying my hand at writing a code that would give me frequency tables for different products by different years. The code I wrote gives me frequencies for all the products instead of selecting the product and year combinations I specify in the macro. Can anybody please correct the program?
Thanks!

data fruits;
set fruitdata;
if date gt 'XXX'd and date lt 'XXX'd then year= '2007';
if date gt 'XXX'd and date lt 'XXX'd then year= '2008';
if date gt 'XXX'd and date lt 'XXX'd then year= '2009';
if fruit= ‘orange’ then product= '1'; else product ='0';
if fruit= ‘apple’ then product= '2'; else product ='0';
if fruit= ‘pear’ then product= '3'; else product ='0';
run;


%macro test(year=, product=);
proc freq data= fruits;
table fruit;
title ‘Sale of product& in year&';
run;
%mend;

%test(year= '2007', product= '1');
%test(year= '2007', product= '2');
%test(year= '2007', product= '3');
%test(year= '2007', product= '1');
%test(year= '2007', product= '2');
%test(year= '2007', product= '3');
%test(year= '2009', product= '1');
%test(year= '2009', product= '1');
%test(year= '2009', product= '1');

[Cynthia_sas]

Hi:
It's not clear to me where you are trying to use &year and &product...other than in your TITLE statement. You have:
[pre]
title ‘Sale of product& in year&';
[/pre]

... which is incorrect...it is not product& and year&, it should be &product and &year (if &PRODUCT and &YEAR are your macro variables). Also, you have your title statement in SINGLE quotes, which is incorrect, macro variable references only resolve in DOUBLE quotes, as shown in the corrected title statement below.

[pre]
title "Sale of &product in &year";
[/pre]

However, I would also expect you to have a WHERE clause or some other selection criteria in your macro program.

This paper is a good introduction to the SAS Macro facility and may help to clear up some of your confusion on this matter:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

At any rate, I wonder whether you need the macro facility at all for this task. A simple change to your PROC FREQ would work, too. When you use BY group processing, you have access to the BY variables to use in a SAS title statement. So if you did your PROC FREQ with a BY statement (BY year product;), then you could use the special BY variables in the SAS title. No need for macro variables at all. For more information about automatic BY variables in the SAS title, see this:
http://www2.sas.com/proceedings/sugi23/Coders/p75.pdf

cynthia
[pre]
data fruits;
set sashelp.prdsale;
where division = 'CONSUMER';
if country = 'GERMANY' then fruit = 'orange';
else if country = 'CANADA' then fruit = 'apple';
else if country = 'U.S.A.' then fruit = 'pear';

if product in ( 'BED','TABLE') then product = '0';
else if product = 'SOFA' then product = '1';
else if product in ('DESK', 'CHAIR') then product = '2';

if year = 1993 then year = 2007;
else if year = 1994 then year = 2008;
run;

proc sort data=fruits;
by year product;
run;

ods listing;
** Use options NOBYLINE to suppress the automatic BYLINE under the title;
/* options NOBYLINE */
proc freq data= fruits;
by year product;
table fruit;
title "Sale for Year #byval1 and Product #byval2";
run;[/pre]

[spg]

You're right...I've muddled things up. I knew the proc reg using "by"...just thought I'd try something different... Thanks for the paper!