DATA Step, Macro, Functions and more

SAS Macro help, creating custom datasets

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

SAS Macro help, creating custom datasets

[ Edited ]

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)

 


Accepted Solutions
Solution
‎10-10-2017 07:59 PM
Super User
Super User
Posts: 7,934

Re: SAS Macro help, creating custom datasets

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


All Replies
Super User
Posts: 23,296

Re: SAS Macro help. Creating datasets

Does the code work if you hardcode the values?

Contributor
Posts: 48

Re: SAS Macro help. Creating datasets

 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

Super User
Posts: 23,296

Re: SAS Macro help. Creating datasets

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;

 

 

 

 

Respected Advisor
Posts: 4,678

Re: SAS Macro help. Creating datasets

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

Super User
Posts: 23,296

Re: SAS Macro help. Creating datasets


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. 

 

 

Frequent Contributor
Posts: 113

Re: SAS Macro help. Creating datasets

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.

Respected Advisor
Posts: 4,678

Re: SAS Macro help. Creating datasets

Posted in reply to ShiroAmada

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

 

 

Super User
Posts: 6,632

Re: SAS Macro help, creating custom datasets

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.

Super User
Posts: 23,296

Re: SAS Macro help, creating custom datasets

Posted in reply to Astounding

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

Contributor
Posts: 48

Re: SAS Macro help, creating custom datasets

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.

 

 

Solution
‎10-10-2017 07:59 PM
Super User
Super User
Posts: 7,934

Re: SAS Macro help, creating custom datasets

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.

Super User
Posts: 23,296

Re: SAS Macro help, creating custom datasets

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. 

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 272 views
  • 9 likes
  • 6 in conversation