How to create this macro with conditions?

Question

Sample input data:
FirstName LastName Group Age LastVenue Position
Jack Smith ULDA 25 TheaterA 1
Jesse James GODL 37 TheaterB 12
Jane Doe ULDA 29 TheaterA 3
Izzy Gord IIPA 41 TheaterC 8
Ann Roswell GODL 30 TheaterB 16
Chelsea Jenk ULDA 19 TheaterA 11

I am trying to create:
%macro group_members(group=); proc print data=sample; var Position Age Group FirstName LastName; where group=&group; %mend group_members;

However I want to add conditions to it so if nothing is entered %group_members() then it will display all groups with the order of the variables shown above. If an invalid group is entered in this case: %group_members(LOL) then I would like a note to be sent to the log %put 'An invalid group was entered'. and therefor nothing should be printed. I am trying to create a program very similar on a much larger dataset.
I appreciate any help in advanced! Thank you :)

So far I have tried:
%macro group_members(group=); proc sql; select count(*) into :ct from sample where group="&group" quit; proc print data=sample; %if &group ^= %then %do; where group="&group."; %end; %if &ct = 0 %then %put An Invalid group was entered; %else %do; where group="&group."; %end; run; %mend group_members;

I get errors from every test.. for example %group_members() returns an error of:
ERROR: More positional parameters found than defined

score 1 · Answer 1 · answered Feb 24 '15 at 01:10

1

Entering a blank resulting in all groups being shown could be achieved by surrounding the where statement with this macro code:

    %if &group ^= %then %do;
      where group="&group.";
    %end;

This only submits the where statement, in the event that the &group variable is populated. Note also that I've added double quotes so that the where statement doesn't generate syntax errors.

The macro would need to know which groups were or were not valid. This would require an extra processing step before the proc print:

    proc sql;
      select count(*) into :ct
      from sample
      where group="&group";
    quit;

    %if &ct = 0 %then %put An invalid group was entered;
    %else %do;
    ...

&ct will contain the number of records that match the where clause. If zero, then I'm assuming that means it's an invalid group.

answered Feb 24 '15 at 01:10

mjsqu

5,151
1
17
21

Thank you! I am new to SAS Macro. Would I place the proc sql; inside the macro before the proc print? – Jax Feb 24 '15 at 01:17
Yes, the `&group` macro variable isn't available outside the macro, so it makes no sense for it to be outside. – mjsqu Feb 24 '15 at 01:18
I tried this but I can not get it to work... This is what I have: `%macro group_members(group=); proc sql; select count(*) into :ct from sample where group="&group" quit; proc print data=sample; %if &group ^= %then %do; where team="&group."; %end; %if &ct = 0 %then %put An Invalid group was entered; %else %do; where group="&group."; %end; run; %mend group_members; %group_members()` – Jax Feb 24 '15 at 02:26
One error I get when I try to use an actual group name such as `ULDA` is: `ERROR: More positional parameters found than defined` – Jax Feb 24 '15 at 02:28
@Jax remove `=` in first line; – Lovnlust Feb 24 '15 at 03:10

Lovnlust · Answer 2 · 2015-02-24T05:15:55.687

0

Credit to @mjsqu

Step1: Test if &group is a valid group. count(*) do this for you.

Step2: If count(*) return 0, then output user-defined messages.

Step3: Otherwise, continue proc print. If &group = then list all records.

%macro group_members(group);
    proc sql noprint; 
    select count(*) into :ct 
    from sample 
    where group="&group."; 
    %if &ct = 0 and &group ne %then %put An Invalid group was entered; 
    %else %do;
    proc print data=sample;
    var Position Age Group FirstName LastName;
    %if &group ne %then 
    %do;
    where group="&group."; 
    %end;
    %end;
%mend group_members;
%group_members();
%group_members(GODL);
%group_members(G);

edited Feb 24 '15 at 05:15

answered Feb 24 '15 at 03:09

Lovnlust

1,507
3
29
53

I would suggest add `noprint` after `proc sql`. It will hide the value of `&ct` on top of `proc print` results. – Lovnlust Feb 24 '15 at 03:10
When Group would be blank, your code would still print - An invalid group was entered, also it would not output anything, which I don't think what OP is looking for. – in_user Feb 24 '15 at 05:05

in_user · Answer 3 · 2015-02-24T04:12:29.837

/Creating Sample dataset/

data test;
infile datalines dlm="," missover;
input FirstName : $10.
      LastName : $10.
      Group : $8.
      Age : 8.
      LastVenue : $10.
      Position : 8.
      ;
      datalines;
Jack,Smith,ULDA,25,TheaterA,1
Jesse,James,GODL,37,TheaterB,12
Jane,Doe,ULDA,29,TheaterA,3
Izzy,Gord,IIPA,41,TheaterC,8
Ann,Roswell,GODL,30,TheaterB,16
Chelsea,Jenk,ULDA,19,TheaterA,11
;
run;

Have added comments in the code itself

%macro group_members(group=); 
%put &group.;

/*Checking if the group is valid or invalid*/
proc sql noprint;
select count(*) into :num from test where group="&group.";
quit;
%put &num.;


data final;
set test;

/*checking if the group entered is NULL, if it is ,then it will output all the records*/
%if "&group."="" %then %do; %end;


/*If the group is Valid or not, if it is invalid then nothing will be in output and a msg in the LOG will be displayed, you can put ERROR statement if you want*/
%else %if &num. = 0 %then %do;
where group="&group.";
%put "An Invalid group was entered";

/*If above two are not the case then it will filter on that group*/
%end;
%else %do;
where group="&group.";
%end;
run;

%mend group_members;

%group_members(group=);

How to create this macro with conditions?

3 Answers3