topic Re: Creating a macro with proc sql. in SAS Programming

Creating a macro with proc sql.

Pawel_ — Tue, 05 Jan 2021 14:39:37 GMT

Hello. I'm new to the forum.

I want to create a macro, that for the following data

will display all games for a specific year and specific platform and sum up sales.

My idea was this, but unfortunately it doesn't work.

%MACRO sorting(Year, Platform);
proc sql;
create table want4 as
select Name,Platform, Year, sum(NA_Sales, JP_Sales, Other_Sales) as suma
from games
group by &Year, &Platform
order by suma desc;
quit;
run;
%mend;
%sorting(2010,"PS3")

Is it possible for the result to be a table?

I suppose I'm passing the arguments incorrectly.

Re: Creating a macro with proc sql.

PaigeMiller — Tue, 05 Jan 2021 15:06:35 GMT

Why do you need a macro at all?

PROC SUMMARY or PROC REPORT or PROC TABULATE or PROC SQL will do this without macros.

Re: Creating a macro with proc sql.

Pawel_ — Tue, 05 Jan 2021 15:12:15 GMT

I know, but I have to use macro.

Re: Creating a macro with proc sql.

Tom — Tue, 05 Jan 2021 15:16:00 GMT

Turn on the MPRINT option before calling your macro so that you can see what SAS code it is generating.

Currently it is generating this code, which does not make any sense.

create table want4 as
select Name,Platform, Year, sum(NA_Sales, JP_Sales, Other_Sales) as suma
from games
group by 2010, "PS3"
order by suma desc;

If you want to create the new variable SUMA then just use a data step (you could use PROC SQL also but why?).

data want4 ;
  set games;
  suma = sum(NA_Sales, JP_Sales, Other_Sales) ;
run;

Re: Creating a macro with proc sql.

Tom — Tue, 05 Jan 2021 15:16:27 GMT

@Pawel_ wrote:
I know, but I have to use macro.

????

Re: Creating a macro with proc sql.

PaigeMiller — Tue, 05 Jan 2021 15:16:44 GMT

Default macro, gets the answers while using a macro (pointlessly, but a macro is used)

%MACRO sorting;
proc sql;
create table want4 as
select Name,Platform, Year, sum(NA_Sales, JP_Sales, Other_Sales) as suma
from games
group by Year, Platform
order by suma desc;
quit;
run;
%mend;
%sorting

Didn't someone explain what the macro is supposed to do? Macros make code dynamic, what part of the above should be dynamic?

Re: Creating a macro with proc sql.

mkeintz — Tue, 05 Jan 2021 15:28:53 GMT

@Pawel_ wrote (emphasis mine)::

...

will display all games for a specific year and specific platform and sum up sales.

If by "specific" you mean a single user-specified year and platform, then you would need a "where" clause, not a "group by".

And I don't know what you mean by "Is it possible for the result to be a table?"

And finally, I reply with the most common observation on this forum. Allow me to be recursive:

A problem description that just says "it doesn't work" doesn't work.

Re: Creating a macro with proc sql.

Shmuel — Tue, 05 Jan 2021 15:31:40 GMT

You are asking to extract and sum "games for a specific year and specific platform and sum up sales".

To extract specific info use where. Your macro should look like:

%MACRO sorting(Year, Platform);
proc sql;
create table want4 as
select Name,Platform, Year, 
       sum(NA_Sales, JP_Sales, Other_Sales) as suma
from games (where=(Year=&year and Platform=&palatform))
group by Year, Platform /* probably not needed as one group selected */
order by calculated suma;  /* desc ?? you did not select this variable */
quit;
run;
%mend;
%sorting(2010,"PS3")