BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Haydn
Quartz | Level 8

Hi All,

 

I'm trying to write my 1st SAS Macro (see below).

 

What I'm trying to achieve is create datasets for each Zone  and also create a dataset where the ZoneFilter = 'Eastern Civ' 'Top Civ' 'Locatin2 Civ'.

I can get the macro to work for creating a dataset for each Zone other than the 3 combined ones above.

 

%let OneList='Eastern Civ' 'Top Civ' 'Locatin2 Civ';

%macro zonedata(ZoneFilter_nm=,title=);
 
proc sql;
     create table zonedata_&title. as
 
     SELECT     *
 
from work.tesdata  as a
where a.ZoneFilter = &ZoneFilter_nm
;
quit;
 
%mend zonedata; 
%zonedata(ZoneFilter_nm='Mid State',title=Mid_State)
%zonedata(ZoneFilter_nm='Mid Terr',title=Mid_Terr)
%zonedata(ZoneFilter_nm='Eastern Civ',title=Eastern_Civ)
%zonedata(ZoneFilter_nm='North State',title=North_State)
%zonedata(ZoneFilter_nm='Top Oz',title=Top_Oz)
%zonedata(ZoneFilter_nm='Top Terr',title=Top_Terr)
%zonedata(ZoneFilter_nm='Top Civ',title=Top_Civ)
%zonedata(ZoneFilter_nm='Bottom Oz',title=Bottom_Oz)
%zonedata(ZoneFilter_nm='Bottom Terr',title=Bottom_Terr)
%zonedata(ZoneFilter_nm='Bottomern State',title=Bottomern_State)
%zonedata(ZoneFilter_nm='Locatin1',title=Locatin1)
%zonedata(ZoneFilter_nm='Loc',title=Loc)
%zonedata(ZoneFilter_nm='Locatin2 Civ',title=Locatin2_Civ)
%zonedata(ZoneFilter_nm='Locatin2ern Oz',title=Locatin2ern_Oz)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

For the macro to work it needs to generate valid SAS code. If you want the user to specify multiple values at once then the generated WHERE clause needs to use the IN operator instead of the = operator.

%macro zonedata(ZoneFilter_nm=,title=);
proc sql;
create table zonedata_&title. as
  select a.*
  from work.tesdata a
  where a.ZoneFilter in (&ZoneFilter_nm)
;
quit;
%mend zonedata; 

Now you can call it with multiple values.   

%zonedata
(ZoneFilter_nm='Eastern Civ' 'Top Civ' 'Locatin2 Civ'
,title=Mid_State
)
MPRINT(ZONEDATA):   proc sql;
MPRINT(ZONEDATA):   create table zonedata_Mid_State as select a.* from work.tesdata a where a.ZoneFilter
in ('Eastern Civ' 'Top Civ' 'Locatin2 Civ') ;

Note that the it is much easier to NOT include commas between the items in the list. The IN () operator does not need the commas and it is much easier to pass values into a macro that do not contain commas. If you tried to pass commas as part of the value of a macro parameter then you would need to add something to quote the values to prevent SAS from thinking they marked new macro parameters in the macro call.

View solution in original post

12 REPLIES 12
Reeza
Super User

Does the code work if you hardcode the values?

Haydn
Quartz | Level 8

 Would that be something like

 

%zonedata(ZoneFilter_nm='Eastern Civ' 'Top Civ' 'Locatin2 Civ' ,title=Locatin2ern_Oz)

 

I was told that you cant use "in" via a macro

Reeza
Super User

No. Macros need to generate valid SAS code. Literally replace the macro variables in the code with the values you state don't work. If they don't work in open code, they won't work in a macro either.

 

I don't think I realized what you were trying to do initially, it's still a bit vague, but making some assumptions it sounds like you designed a macro to work for a single value, but now you want it to work with a list of values rather than a single value. You can't use = with multiple values. Like I mentioned, figure out what the PROC SQL code would be without a macro and that will tell you how to change it. 

 

This is clearly homework, which is why I'm not giving you an explicit answer. 

For debugging, you can use the following to get more information in the log. 

 

options mprint symbolgen mlogic;

 

 

 

 

Patrick
Opal | Level 21

@Haydn

"I was told that you cant use "in" via a macro"

Macro's are you automatic typewriters. The macro compiler/processor will only pick-up the bits of your code which it recognizes as macro language, then executes these bits and as a "left over" writes text. This text is then what the SAS compiler picks up for execution (so the text must be valid SAS language). 

Use option mprint; and you'll see in the log what text the macro generates.

 

In your case: You want to use the in operator on SAS language level so you can just use it as is. The macro compiler won't touch it because it's nothing on macro level. 

Reeza
Super User

@Haydn wrote:

 

 

I was told that you cant use "in" via a macro


Yet it's listed in the documentation...

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1alyfc9f4qrten10sd5qz5e1w5q.htm&docset...

 

At one point it wasn't so whoever said that, isn't up to date on this particular feature or using old course notes/examples. 

 

You should also look at the MINOPERATOR and MINDELIMITER and how to set IN to work appropriately. 

 

 

ShiroAmada
Lapis Lazuli | Level 10

Try this...

 

%macro test;
data _null_;
  set sample nobs=n;
  if n then call symputx('n',n);
run;

proc sql noprint;
  select upcase(nm), upcase(tranwrd(strip(nm)," ","_")) 
into :nm1-:nm&n, :ds1-:ds&n from sample;
quit;

%put _user_;

data %do i=1 %to &n.;  &&ds&i %end;   ;
  set sample;
  %do i=1 %to &n.;
    %if %eval(&i=1) %then %do;
        if upcase(nm)="&&nm&i" then output &&ds&i.;
     %end;
    %else %if %eval(&i>1) %then %do;
        else if upcase(nm)="&&nm&i" then output &&ds&i.;
     %end;
  %end;
run;
%mend;

options macrogen;

%test;

 

Hope this helps.

Patrick
Opal | Level 21

@Haydn

Please don't try what @ShiroAmada posted. I'll take what you're doing as an exercise to get into SAS Macro coding so keep it simple for now and try to make this work.

 

On a general level:

It's most of the time not a good idea to split a single data set into multiple data sets. We've got by-group processing so it's most of the time much more efficient to create such groups in a single data set (i.e. by defining and applying formats for grouping).

 

 

Astounding
PROC Star

First, note that macro language does support a list using IN.  There are two options related to permitting this (see MINDELIMITER, for example), however ... you don't need a macro language IN comparison.  You need SQL to use IN, such as:

 

where a.Zonefilter in (&Zonefilter_nm)

 

That would work perfectly well, for either of these values for &ZONEFILTER_NM:

 

'Mid State'

'Eastern Civ'  'Top Civ'  'Locatin2 Civ'

 

You can certainly call the macro using the parameter ZoneFilter_nm = 'Eastern Civ'  'Top Civ'  'Locatin2 Civ'

 

Finally, note that this is a good learning exercise.  However, it runs into a common danger when learning macro language in that the program is designed to be expensive.  Each call to the macro must search through the entire data set TESTDATA, looking for the proper observations.  That can be expensive.  A cheaper, faster plan would be to attempt to generate a single pass through the data.  I can abbreviate that here:

 

data Mid_State Mid_Terr Eastern_Civ;

set testdata;

select (ZoneFilter_nm);

   when ('Mid State') output Mid_State;

   when ('Mid Terr') output Mid_Terr;

   when ('Eastern Civ') output Eastern_Civ;

   otherwise;

end;

run;

 

The idea is to read TESTDATA just once, and create all subsets. That task would be harder.  And the form of the parameters might have to change (and might even be a SAS data set instead of hard-coded translations).  Regardless, there's a saying about macro language:  Easy to use, easy to abuse.  One of the pitfalls is calling a macro multiple times, and having each macro call read in the entire data source.  Many times, there is another path that reads the data source only once.

Reeza
Super User

As an addition to @Astounding excellent suggestion here is a good write up on the various options, with the best practice. But I suspect this is homework, ergo you have no choice in the matter.

 

Best Practice: Just Don't Do It

There is a pretty strong consensus holding that such proliferation of data sets is undesirable, and that the subsets should be maintained together in one inclusive table. The reasoning is that BY statements can be used to stratify processing, WHERE statements can be used for selective processing, and indexes can be created to support both BY and WHERE statements. If the partitioning is proposed to compartmentalize access to different subsets, views may provide a better solution.

However, there are situations where partitioning may still be necessary (maybe the boss said Do It This Way).

 

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

Haydn
Quartz | Level 8

Thanks for the great advice, certainly very helpful and food for thought.

 

These datasets are client listings for each area and will be sent to staff to initiate contact. As per one of your points (the boss said do it this way) that applies here. Boss wants each area to only get their clients contacts. The area's are nation wide, so I can see the logic in that. The staff that these listings will be sent to, do not have access to SAS EG nor the underlying data.

 

I have created these datasets created via a normal datastep, but I was informed by a colleague that using a macro was a better way to create these datsets.

 

 

Tom
Super User Tom
Super User

For the macro to work it needs to generate valid SAS code. If you want the user to specify multiple values at once then the generated WHERE clause needs to use the IN operator instead of the = operator.

%macro zonedata(ZoneFilter_nm=,title=);
proc sql;
create table zonedata_&title. as
  select a.*
  from work.tesdata a
  where a.ZoneFilter in (&ZoneFilter_nm)
;
quit;
%mend zonedata; 

Now you can call it with multiple values.   

%zonedata
(ZoneFilter_nm='Eastern Civ' 'Top Civ' 'Locatin2 Civ'
,title=Mid_State
)
MPRINT(ZONEDATA):   proc sql;
MPRINT(ZONEDATA):   create table zonedata_Mid_State as select a.* from work.tesdata a where a.ZoneFilter
in ('Eastern Civ' 'Top Civ' 'Locatin2 Civ') ;

Note that the it is much easier to NOT include commas between the items in the list. The IN () operator does not need the commas and it is much easier to pass values into a macro that do not contain commas. If you tried to pass commas as part of the value of a macro parameter then you would need to add something to quote the values to prevent SAS from thinking they marked new macro parameters in the macro call.

Reeza
Super User

Since you're only filtering the data, or at least that's what you've shown, you can filter the data on the next proc/data step instead using a WHERE clause. 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 12 replies
  • 2235 views
  • 9 likes
  • 6 in conversation