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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

6 REPLIES 6
Shmuel
Garnet | Level 18

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
Super User

Why do you think you need a macro?

Malathi13
Obsidian | Level 7
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
Super User

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
Super User Tom
Super User

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
Obsidian | Level 7

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

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
  • 6 replies
  • 2439 views
  • 1 like
  • 4 in conversation