Macro help

[Malathi13]

Hi,

I have a data (Feedback) with customer, score and comments variables and I want to write a macro that will programmatically call %subset for each customer value in Feedback.

Feedback
CUSTOMER	SCORE	COMMENT
A	3	The is no parking
A	5	The food is expensive
B	.	I like the food
C	5	It tastes good
C	.
C	3	I like the drink
D	4	The dessert is tasty
D	2	I don't like the services
[additional rows not shown]

%macro subset( cust=);

proc print data= feedback;

where customer = "&cust";

run;

%mend;

How can I get the value for each customer using %subset? Since I don't know how many customers are there, is there a easy way to write a macro for n customers?

Also I want to write a program to find out which customer has given a "5" score immediately followed by a missing score and find out which customer has commented on “parking” and “expensive” . Since there are addititional rows that are not shown writing a macro should help in finding all the customers who commented on parkig and expensive.

I really appreciate all the help from you all.

Thank you

M

[Shmuel]

You don't need a macro.

Sort your dataset and print by customer:

proc sort data=feedback; by customer; run;

proc print data=feedback;
  by customer;
run;

you may wish create each customer's report starting a new page,

then add line to the print procedure:

proc print data=feedback;
  by customer;
  pageby customer;
run;

you can even print summary by adding:  sumby customer;

[Reeza]

Why do you think you need a macro?

[Malathi13]

Hi Reeza,
I want to see if a macro is more efficient in getting the values for each customer and I want to learn macro with this simple dataset.

Thank you
M

[Reeza]

If you're trying to learn then I would suggest the following two posts on how to split data.

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

http://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

In general, if you have a process where you want to call a macro multiple times I recommend CALL EXECUTE instead of macro loops or multiple calls. The documentation has an example of a macro and calling it using call execute. In fact, I think it's pretty close to your requirements above.

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n1q1527d51eivsn1o...

[Tom · Accepted solution]

Most of those questions don't sound like issues for macro coding.

Now if you want to conditionally generate macro calls using data from your table it is probably going to be much easier doing that using a data step, whether the data step is part of macro or not.

First let's turn your sample into an actual dataset.

data feedback ;
  input CUSTOMER $ score comment $40. ;
cards;
A 3 The is no parking
A 5 The food is expensive
B . I like the food
C 5 It tastes good
C . . 
C 3 I like the drink
D 4 The dessert is tasty
D 2 I don't like the services
;

And make a simple macro that takes one customer ID as input.

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

Now you can generate macro calls from the data using a simple data step.  Use BY group processing to call it only once per customer.

data _null_;
  set feedback;
  by customer ;
  if first.customer;
  call execute(cats('%nrstr(%print_subset)(',customer,')'));
run;

The other questions are about data processing, not macro programming.

which customer has given a "5" score immediately followed by a missing score 

data subset1;
  do until (last.customer);
    set feedback ;
    by customer ;
    if score=. and prev_score=5 then found=1;
    prev_score=score;
  end;
  if found;
  keep customer;
run;

find out which customer has commented on “parking” and “expensive” 

data subset2;
  merge feedback(in=in1 where=(lowcase(comment) like '%parking%'))
        feedback(in=in2 where=(lowcase(comment) like '%expens%'))
  ;
  by customer;
  if first.customer and in1 and in2;
  keep customer;
run;

[Malathi13]

Hi Tom,

Thank you so much for the perfect answers, I really appreciate your help and the time you invested in helping me in the program.

Thank you so much again.

M