0

I have a customer survey data like this:

data feedback;
    length customer score comment $50.;
    input customer $ score  comment & $;
    datalines;
A 3 The is no parking
A 5 The food is expensive
B . I like the food
C 5 It tastes good
C . blank                  
C 3 I like the drink
D 4 The dessert is tasty
D 2 I don't like the service
;
run;

There is a macro code like this:

%macro subset( cust=);

    proc print data= feedback;
        where customer = "&cust";
    run;

%mend;

I am trying to write a program that call the %subset for each customer value in feedback data. Note that we do not know how many unique values of customer there are in the data set. Also, we cant change the %subset code.

I tried to achieve that by using proc sql to create a unique list of customers to pass into macro code but I think you cannot pass a list in a macro code. Is there a way to do that? p.s I am beginner in macro

Reeza
  • 20,510
  • 4
  • 21
  • 38
SidraKh
  • 9
  • 2
  • Recommended reading: https://analytics.ncsu.edu/sesug/2016/BB-229_Final_PDF.pdf – user667489 Jan 24 '19 at 15:54
  • 2
    Possible duplicate of [Dynamically call macro from sas data step](https://stackoverflow.com/questions/25545892/dynamically-call-macro-from-sas-data-step) – Reeza Jan 24 '19 at 15:55
  • You're looking for `call execute` which allows you to call it from a data step. – Reeza Jan 24 '19 at 15:55

2 Answers2

1

I like to keep things simple. Take a look at the following:

data feedback;
length customer score comment $50.;
input customer $ score  comment & $;
datalines;
A 3 The is no parking
A 5 The food is expensive
B . I like the food
C 5 It tastes good
C . blank                  
C 3 I like the drink
D 4 The dessert is tasty
D 2 I don't like the service
;
run;

%macro subset( cust=);
    proc print data= feedback;
       where customer = "&cust";
    run;
%mend subset;

%macro test;
   /* first get the count of distinct customers */
   proc sql noprint;
      select count(distinct customer) into : cnt
         from feedback;quit;

   /* do this to remove leading spaces */
   %let cnt = &cnt;

   /* now get each of the customer names into macro variables
   proc sql noprint;
       select distinct customer into: cust1 - :cust&cnt
            from feedback;quit;

/* use a loop to call other macro program, notice the use of &&cust&i */
%do i = 1 %to &cnt;
   %subset(cust=&&cust&i);
%end;
%mend test;
%test;

of course if you want short and sweet you can use (just make sure your data is sorted by customer):

data _null_;
set feedback;
by customer;
if(first.customer)then call execute('%subset(cust='||customer||')');    
run;
DCR
  • 14,737
  • 12
  • 52
  • 115
0

First fix the SAS code. To test if a value is in a list using the IN operator, not the = operator.

where customer in ('A' 'B')

Then you can pass that list into your macro and use it in your code.

%macro subset(custlist);
proc print data= feedback;
  where customer in (&custlist);
run;
%mend;
%subset(custlist='A' 'B')

Notice a few things:

  1. Use quotes around the values since the variable is character.
  2. Use spaces between the values. The IN operator in SAS accepts either spaces or comma (or both) as the delimiter in the list. It is a pain to pass in comma delimited lists in a macro call since the comma is used to delimit the parameters.
  3. You can defined a macro parameter as positional and still call it by name in the macro call.

If the list is in a dataset you can easily generate the list of values into a macro variable using PROC SQL. Just make sure the resulting list is not too long for a macro variable (maximum of 64K bytes).

proc sql noprint;
  select distinct quote(trim(customer))
    into :custlist separated by ' '
    from my_subset
  ;
quit;
%subset(&custlist)
Tom
  • 47,574
  • 2
  • 16
  • 29