topic Re: Macro help in SAS Programming

Macro help

Malathi13 — Thu, 27 Jul 2017 18:38:26 GMT

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

Re: Macro help

Shmuel — Thu, 27 Jul 2017 19:03:11 GMT

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;

Re: Macro help

Reeza — Thu, 27 Jul 2017 19:13:36 GMT

Why do you think you need a macro?

Re: Macro help

Malathi13 — Thu, 27 Jul 2017 19:31:55 GMT

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

Re: Macro help

Reeza — Thu, 27 Jul 2017 19:37:42 GMT

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#n1q1527d51eivsn1ob5hnz0yd1hx.htm

Re: Macro help

Tom — Thu, 27 Jul 2017 19:51:54 GMT

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;

Re: Macro help

Malathi13 — Thu, 27 Jul 2017 20:22:32 GMT

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.