turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How to write a macro which can handle multiple val...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

4 weeks ago

Hi all,

Can anyone help me with the following scenario?.

Consider a Sales Data. Suppose you have 3 Types of Orders and a variable Order_type having values either Type 1 Type 2 or Type 3 for Each Order Placed and you have another variable Quantity, having the total number of quantities ordered for each order. Write a Macro to display the frequency of quantity ordered for given order type in any particular year.

__For example:__ If I mention value of year 2015 and type 3, It should display frequency of quantities placed in 2015 with type 3 with appropriate title. If I just mention year 2015, It should display frequency of all quantities ordered in 2015 with all types.

```
%macro freq(year=,type=);
proc sql;
select product_id,total_sales,order_type,year from(
select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data
group by 1,2,3)
where year=&year or order_type=&type;
quit;
%mend;
%freq(year=2007,type=1); /*to print the values of order_type=1 and year=2007*/
```

The 2nd parameter: “type” is optional. The code should be able to handle this case, which it is not handling currently.

Any help is appreciated.

Thank you.

Accepted Solutions

Solution

4 weeks ago

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

4 weeks ago

i made a small change to the code and it worked.

i've put 'and' instead of 'or'

%macro freq(year=,type=);

proc sql;

select product_id,total_sales,order_type,year from(

select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data

group by 1,2,3)%if %length(&type)>0 %then %do;

where year=&year and order_type=&type;%end;%else %do;where year=&year;%end;

quit;

%mend;

%freq(year=2007);

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

4 weeks ago

will this help?

```
%macro freq(year=,type=);
proc sql;
select product_id,total_sales,order_type,year from(
select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data
group by 1,2,3)
```

%if %length(&type)>0 %then %do;
where year=&year or order_type=&type;

%end;

%else %do;

where year=&year;

%end;
quit;
%mend;
%freq(year=2007,type=1); /*to print the values of order_type=1 and year=2007*/

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

4 weeks ago - last edited 4 weeks ago

This is essentially the same suggestion, but simplified a little (where you see two semicolons in a row, be sure to include both):

```
%macro freq(year=,type=);
proc sql;
select product_id,total_sales,order_type,year from(
select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data
group by 1,2,3) where year=&year %if %length(&type)>0 %then or order_type=&type;
```

;
quit;
%mend;
%freq(year=2007,type=1); /*to print the values of order_type=1 and year=2007*/

Solution

4 weeks ago

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

4 weeks ago

i made a small change to the code and it worked.

i've put 'and' instead of 'or'

%macro freq(year=,type=);

proc sql;

select product_id,total_sales,order_type,year from(

select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data

group by 1,2,3)%if %length(&type)>0 %then %do;

where year=&year and order_type=&type;%end;%else %do;where year=&year;%end;

quit;

%mend;

%freq(year=2007);