Macro Question

Accepted Solution Solved
Reply
Contributor krm
Contributor
Posts: 26
Accepted Solution

Macro Question

Hi, 

 

I am fairly new to creating macros in SAS. I have a similar dataset as below: 

 

data teams;
input
team $5. wins year ;
format
team $5.;
datalines;
TeamA 10 2013
TeamA 15 2014
TeamA 32 2015
TeamB 2 2013
TeamB 17 2014
TeamB 5 2015
;

 

 

I want to create seperate tables for each team using proc sql by pulling the team name from the table above. I don't want to copy and paste the same code all the time for every team. So I thought a macro would be more functional. 

 

proc sql;
create table teamA as
select team, year, sum(wins) as total
from teams
where team = 'TeamA'
group by 1,2;
quit;

 

So in this case there would be two datasets, a TeamA and a TeamB.

 

 

I know I may get the same results without using a macro, but just to understand how a macro works I wanted to ask the question. 

 

Thank you very much for the help!

 

 

 


Accepted Solutions
Solution
‎12-03-2015 02:23 PM
Frequent Contributor
Posts: 130

Re: Macro Question

This should do what you're looking for.  To understand what the call symputx function does, I would recommend reading up on it so you know how the macro is executing.  Hope this helps!

 

data teams;
input team$ wins year;
datalines;
TeamA 10 2013
TeamA 15 2014
TeamA 32 2015
TeamB 2 2013
TeamB 17 2014
TeamB 5 2015
;
run;

proc sql;
create table unique_teams as
select distinct team
from teams;
quit;

%macro create;
data _NULL_;
set unique_teams end=last;
call symputx(cats("team",_N_),team);
if last then call symputx("n",_N_);
run;

%do i=1 %to &n;
proc sql;
create table &&team&i as
select team, year, sum(wins) as total
from teams
where team="&&team&i"
group by team, year;
quit;
%end;
%mend;
%create;

View solution in original post


All Replies
Super User
Posts: 5,366

Re: Macro Question

This would take a few steps, so it's not necessarily the best learning exercise.  But let's at least outline an approach.

 

First, get your TEAM values into a single macro variable:

 

proc sql noprint;

select distinct team into : teamlist separated by ' ' from teams;

quit;

 

Then write a macro that can loop through every value in the list.  That's a bit harder, so here is a decent reference to study:

 

http://blogs.sas.com/content/publishing/2015/01/30/sas-authors-tip-getting-the-macro-language-to-per...

 

Along the way, you will encounter issues such as possibly switching from single quotes to double quotes.

 

Other approaches are possible (such as CALL EXECUTE), but won't be as useful for learning macro language.

 

Good luck.

Contributor krm
Contributor
Posts: 26

Re: Macro Question

@Astounding thank you for sharing the link, very helpful!

Solution
‎12-03-2015 02:23 PM
Frequent Contributor
Posts: 130

Re: Macro Question

This should do what you're looking for.  To understand what the call symputx function does, I would recommend reading up on it so you know how the macro is executing.  Hope this helps!

 

data teams;
input team$ wins year;
datalines;
TeamA 10 2013
TeamA 15 2014
TeamA 32 2015
TeamB 2 2013
TeamB 17 2014
TeamB 5 2015
;
run;

proc sql;
create table unique_teams as
select distinct team
from teams;
quit;

%macro create;
data _NULL_;
set unique_teams end=last;
call symputx(cats("team",_N_),team);
if last then call symputx("n",_N_);
run;

%do i=1 %to &n;
proc sql;
create table &&team&i as
select team, year, sum(wins) as total
from teams
where team="&&team&i"
group by team, year;
quit;
%end;
%mend;
%create;
Trusted Advisor
Posts: 1,116

Re: Macro Question

 

@dcruik: Just a suggestion: Your code could be simplified by creating the macro variables TEAM1, TEAM2, ... directly in one PROC SQL step rather than one PROC SQL plus one DATA _null_ step:

proc sql noprint;
select distinct team
into :team1 - :team9999 /* This will _not_ create 9999 macro variables */
from teams;
quit;

After the above step, the number &n is automatically available in &SQLOBS, so you could write:

 

...
%do i=1 %to &sqlobs;
...

And let's hope the real teams' names happen to be valid SAS dataset names ... Smiley Happy

Respected Advisor
Posts: 3,156

Re: Macro Question

[ Edited ]

Along the way when you become an experienced programmer, most likely you will develop something I call 'best approach instinct'. We all know there are more than one way to skin a cat (no offense to cat lover), it is especially true in SAS. Putting aside the fact that you are learning Macro language, my instinct tells me Macro is unnecessary for your problem. If your RAM allows (most likely it will in your case), Hash approach is offen used to split and summarize data DYNAMICALLY (sorry for the caps) with unpar efficiency.

 

data teams;
	input
		team $5. wins year;
	format
		team $5.;
	datalines;
TeamA 10 2013
TeamA 2.5 2013
TeamA 15 2014
TeamA 32 2015
TeamB 2 2013
TeamB 17 2014
TeamB 5 2015
;

data _null_;
	if _n_=1 then
		do;
			dcl hash h();
			h.definekey('team','year');
			h.definedata('team','year','total');
			h.definedone();
			call missing(total);
		end;

	/*assume your data is presorted by team*/
	do until(last.team);
		set teams;
		by team;

		if h.find()=0 then
			total+wins;
		else total=wins;
		rc=h.replace();
	end;

	rc=h.output(dataset:team);
	rc=h.clear();
run;

 

Contributor krm
Contributor
Posts: 26

Re: Macro Question

@Haikuo thank you for sharing a different approach that I have never used before, definitely a new experience I'll try to understand the logic of the code. 

Super User
Posts: 11,134

Re: Macro Question

A data driven non-macro approach:

proc sort data=teams; by team year;run;

data _null_;
   set teams;
   by team;
   if first.team then do;
      call execute("data "||team||";");
      call execute ("set teams (where=(team ='"||strip(team)||"')); run;");
   end;
run;
Super User
Posts: 19,157

Re: Macro Question

You have a demo of each of the solutions out there, but I'd like to mention that the best practice is to not do this. SAS allows BY processing in many procs that will allow you to more efficiently process your data. So I have to ask - why do you want to separate your data? If you can provide that answer, most likely someone can provide a better method for what you're trying to do Smiley Happy
Contributor krm
Contributor
Posts: 26

Re: Macro Question

@Reeza  May be I should have posted more specifically what I wanted to do with the data &colonSmiley Wink.

All the replies however have been very helpful. 

 

So I wanted learn a sample macro code to use for any generic application. As I mentioned I am new to writing macros, so I am trying to understand the logic behind the macros in SAS. For example, I used @dcruik's solution to create seperate graphs. 

 

%macro graph;
data _NULL_;
set unique_teams end=last;
call symputx(cats("team",_N_),team);
if last then call symputx("n",_N_);
run;

%do i=1 %to &n;
PROC GPLOT DATA = &&team&i;
PLOT year * total /
VAXIS=AXIS1
HAXIS=AXIS2
FRAME;
RUN; QUIT;
%end;
%mend;
%graph;

 

As you mentioned however there is a by processing even for proc graph so in this case a macro is not really necessary. 

 

 

Super User
Posts: 19,157

Re: Macro Question

Ok...if you're looking to practice, a situation where a macro MIGHT be necessary is to create a report where each page has a table for the team and a graph. But I'd recommend you use a WHERE clause with macro variables rather than using multiple data sets.
PROC Star
Posts: 1,673

Re: Macro Question

If you know how many teams you have, the simplest is just:

 

data TEAMA

        TEAMB;

  set TEAMS;

  if TEAM='TEAMA' then output TEAMA;

  if TEAM='TEAMB' then output TEAMB;

run;

PROC Star
Posts: 1,673

Re: Macro Question

[ Edited ]

Now, looking at your sum of wins.

From a performance viewpoint it is important to only read the source data once.
So if your table is large, you summarise it once *and then* create the team tables.

You can use call execute as shown above or macro variables as below to split the summary.

data TEAMS;

  input TEAM $5. WINS YEAR ;

datalines;

TeamA 10 2013

TeamA 15 2014

TeamA 32 2015

TeamB 2 2013

TeamB 17 2014

TeamB 5 2015

run;

proc summary data =TEAMS nway;

  class TEAM YEAR;

  var WINS;

  output out=TEAMS_SUM sum=;

run;

proc sql noprint;

  select unique TEAM into :teams separated by ' ' from TEAMS_SUM;

  select unique 'if TEAM="'||trim(TEAM)||'" then output ' || TEAM || ';'

         into :dispatch separated by ' ' from TEAMS_SUM;

quit;

data &teams.;

  set TEAMS_SUM;

   &dispatch.;

run;

 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 672 views
  • 5 likes
  • 8 in conversation