Help using Base SAS procedures

Autmation with Macros Part 2

Reply
Frequent Contributor
Posts: 90

Autmation with Macros Part 2

Hello Everyone,

So I figured out the first part to my question which was to modify this code:

proc sql;

title '2007 Purchases by Country';

   select distinct c.Country,

          &Purchases as Purchases label='Purchases'

      from orion.Order_Fact as o,

           orion.Customer as c

      where o.Customer_ID=c.Customer_ID and

            year(Order_Date)=2007

            group by c.Country

            having &Purchases>1

      order by calculated Purchases desc;

quit;

To where it would write out the values of Country and Purchases for the first returned row into macro variables called Country and Country_Purchases, which this is what I got:

%let Purchases=sum(Total_Retail_Price);

proc sql noprint;

title '2007 Purchases by Country';

   select distinct c.Country, &Purchases as Purchases

      into:Country1, :Country_Purchases1

      from orion.Order_Fact as o,

           orion.Customer as c;

quit;

So the next part of my problem is to modify this code:

proc sql;

title '2007 Purchases by Country';

   select distinct c.Customer_Name,

          &Purchases as Purchases label='Purchases' format=dollar10.2

      from orion.Order_Fact as o,

           orion.Customer as c

      where o.Customer_ID=c.Customer_ID and

            year(Order_Date)=2007

            and c.Country='US'

            group by c.Customer_Name

            having &Purchases>1

      order by calculated Purchases desc;

quit;

Which I modified it to this:

proc sql;

title1 "2007 &Country1 Customer Purchases";

title2 "Total &Country1 Purchases: &Country_Purchases";

   select distinct c.Customer_Name,

          &Purchases as Purchases label='Purchases' format=dollar10.2

      from orion.Order_Fact as o,

           orion.Customer as c

      where o.Customer_ID=c.Customer_ID and

            year(Order_Date)=2007

            and "&Country1"=c.Country

            group by "&Country1", c.Customer_ID

            having &Purchases>1

      order by calculated Purchases desc;

quit;

The problem I am having now is that I am only producing one countries reports, which is AU for Australia, and not all of the countries reports on separate pages.

So I am not sure if I went wrong with the first part of my modified code or if I am going wrong with my second query.

I know this is a long post, but if anyone could help me with this, it would be greatly appreciated!

Thanks!!

Alisa

Super User
Posts: 10,497

Autmation with Macros Part 2

The line

and "&Country1"=c.Country

in your where clause says to only select that one value for county. I assume that you have &County1 as 'AU'.

If you want the country name in the title you will need to loop through the possible number of countries. Depending on your calling code either with some fragment like

%do i = 1 %to &numcountries; /* which will require you to know the number of countries */

<your code here replaceiy &County1 with &&Country&i   AFTER you have somehow made a group of Macro variables

Country1 to CountryN >

%end;

Another approach would be to build a single macro variable with a list of country names and use %SCAN and a %do %while or %until to place each name into a temporary macro variable.

Frequent Contributor
Posts: 90

Autmation with Macros Part 2

HI Ballard,

I am not sure what you mean since I haven't really done a do loop with a macro yet:

This is what I have attempted so far:

%macro DO_Country;

%DO I=1 %TO 7;

%END;

%MEND DO_Country;

I am not sure if this is right or what I am doing wrong.

Could you explain what you mean by your earlier post more?

Thanks,

Alisa

Frequent Contributor
Posts: 90

Autmation with Macros Part 2

What about this macro?

%macro COUNTRY;

%DO COUNTRY=1 %TO 7;

WHERE COUNTRY=&COUNTRY;

%END;

%MEND COUNTRY;

Super User
Posts: 10,497

Autmation with Macros Part 2

Something more like the following.

This would be more useful after adding parameters to the macro to reference things like the dataset names and your other macro variables.

%macro country ;

%local Countries NumCountries I Country;
proc sql noprint;
   select distinct country into :Countries separated by ' '
   from <your dataset name with countries goes here>;
quit;

%let NumCountries = %sysfunc(countw(&Countries,' ,'));
%do i = 1 %to &numcountries;
   %let Country = %scan(&countries,&i);
   proc sql;
   title1 "2007 &Country Customer Purchases";
   title2 "Total &Country Purchases: &Country_Purchases";
   /* you didn't provide any information about where the macro variable
      &County_Purchases comes from so this code is likely not to work as is
      I would recommend testing this part to loop through the countries without
      it first, may have problem with &Purchases as well for same reason*/
      select distinct c.Customer_Name,
             &Purchases as Purchases label='Purchases' format=dollar10.2
         from orion.Order_Fact as o,
              orion.Customer as c
         where o.Customer_ID=c.Customer_ID and
               year(Order_Date)=2007
               and "&Country"=c.Country
               group by "&Country", c.Customer_ID
               having &Purchases>1
         order by calculated Purchases desc;
   quit;
%end;
%mend;

Frequent Contributor
Posts: 90

Autmation with Macros Part 2

Hi Ballard,

Why did you put the SQL code within the macro?

The Country_Purchases came from the code that I posted earlier which was my modification of my first code:

proc sql noprint;

title '2007 Purchases by Country';

   select distinct c.Country, &Purchases as Purchases

      into:Country1, :Country_Purchases1

      from orion.Order_Fact as o,

           orion.Customer as c;

quit;

I tried your solution, but I cannot see anything since the SQL is programmed into the macro.

Thanks,

Alisa

Super User
Super User
Posts: 6,499

Re: Autmation with Macros Part 2

You are still referencing the VALUE of the COUNTRY variable in the GROUP BY clause of your second SQL query.  Instead you should use the NAME of the variable.

What is the overall goal of this programming task?

If you want to produce a report by country then use PROC PRINT or PROC REPORT.

PROC SQL is not really very well suited for making reports.

proc sql NOPRINT ;

  create view customer_orders as

    select distinct

       c.country

      ,c.Customer_Name

      ,year(o.order_date) as year

      ,&Purchases as Purchases label='Purchases' format=dollar10.2

    from orion.Order_Fact as o

       , orion.Customer as c

    where o.Customer_ID=c.Customer_ID

      and year(Order_Date)=2007

    group by c.country

           , c.Customer_ID

           , year(o.order_date)

    having &Purchases>1

    order by c.country

           , c.customer_name

           , calculated Purchases desc

  ;

quit;

title1 "#byval(year) #byval(country) Customer Purchases";

proc print data=customer_orders noobs;

  by year country;

  pageby year country;

  var customer_name purchases;

  sum purchases ;

run;

Frequent Contributor
Posts: 90

Autmation with Macros Part 2

Hi Tom,

The thing is that I need to create my output using SQL. I know that PROC PRINT and PROC REPORT are better, but the assignment calls for the use of my second SQL code:

proc sql;

title '2007 Purchases by Country';

   select distinct c.Customer_Name,

          &Purchases as Purchases label='Purchases' format=dollar10.2

      from orion.Order_Fact as o,

           orion.Customer as c

      where o.Customer_ID=c.Customer_ID and

            year(Order_Date)=2007

            and c.Country='US'

            group by c.Customer_Name

            having &Purchases>1

      order by calculated Purchases desc;

quit;

And to modify this code to where it has the macros of Country and Country_Purchases in replacement of Country and Purchases.

Where I am stuck is how to do this so it produces reports for every country.

I know there was the earlier suggestion of using a DO loop within a macro, but I am not sure how to accomplish this.

Super User
Super User
Posts: 6,499

Autmation with Macros Part 2

What is the intent of the macro variables COUNTRY and PURCHASES (more properly macro parameters once you have created your macro)?

Is purchases supposed to be a list of the type of purchases to include in the total for a customer? 

How is the source data stored?  Is there variable PurchaseType that you can query?  Are the different types of Purchases stored as separate variables?

To get SQL to generate separate pages you will need wrap the SQL code in a macro.

When you say you cannot see the SQL with the macro that might be because you need to understand that you first define the macro and then execute it.

You can use the MPRINT option to have SAS display the code that the macro has generated.

Frequent Contributor
Posts: 90

Autmation with Macros Part 2

Country is supposed to Store all of the Countries from orion.Customers and Country_Purchases is to take over from the earlier macro that was defined as Purchases which is a sum(Total_Retail_Price).

Where I am starting to get really, really confused with all of this is if I am to use the macro with the SQL in it, how am I supposed to plug that back into the second code that I need....

I'm about to give up on this problem because there just does not seem to be an easy solution to this...

Super User
Posts: 10,497

Autmation with Macros Part 2

If you are confused at this point I think it may be time to go back to writing down what the intended output and the input condtions. Do not worry about existing code. Do the entire process for some examples by hand, or at least sketch them.

It looks like you are hung up on using existing code and sometimes you need to start from scratch.

InfoAlisaA wrote:

Country is supposed to Store all of the Countries from orion.Customers

That does not look like your request. The macro variable Countries in the code snippet I provided does that. What are you going to do after you have the countries?

"Plug back" into which code and what is the expected result?

Super User
Posts: 9,676

Autmation with Macros Part 2

Tom,

One thing to correct.

if You want use #byval in Title statement, then you also need "options nobyline;" to suppress this value.

Ksharp

Ask a Question
Discussion stats
  • 11 replies
  • 211 views
  • 6 likes
  • 4 in conversation