BookmarkSubscribeRSS Feed
InfoAlisaA
Calcite | Level 5

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

11 REPLIES 11
ballardw
Super User

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.

InfoAlisaA
Calcite | Level 5

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

InfoAlisaA
Calcite | Level 5

What about this macro?

%macro COUNTRY;

%DO COUNTRY=1 %TO 7;

WHERE COUNTRY=&COUNTRY;

%END;

%MEND COUNTRY;

ballardw
Super User

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;

InfoAlisaA
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;

InfoAlisaA
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

InfoAlisaA
Calcite | Level 5

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

ballardw
Super User

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?

Ksharp
Super User

Tom,

One thing to correct.

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

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 922 views
  • 6 likes
  • 4 in conversation