Generating a report by using single SAS macro

[SN1]

Hi,

Can anyone help me find what is the mistake in the following code. Please find the attachment of requirements and data for creating this macro.

(I created this macro using SAS 9.4)

Thanks in advance.

%macro profile (today=, state=, age=, year=);
proc report data=profile nowd colwidth=10 spacing=5 headline headskip;
column Acct_ID Name Age Balance Last_Tran_Date ;
define Last_Tran_Date / format=ddmmyy10.;
compute before;
line @16 "title: Detail Listing Of Account" @68 "Run Date: &today ";
line @16 "state: &state " @45 "Age: <= &age " @68 "Tran_year: &year ";
line " ";
endcomp;
compute after; 
line 60* "_";
line @40 'total balance ='
balance.sum dollar6.;
endcomp;
where (age <= &age and year=&year) and state=&state;
run;
%mend;

%profile (today= %sysfunc(today(),date9.), state=('CA','NY'), age=40, year=2016);

[PaigeMiller]

Step 1: place the following command at the top of your program.

options mprint;

Step 2: run the code and see what errors show up in the SASLOG and see if you can figure out yourself what the problem is.

Step 3: if you can't figure out what went wrong from the SASLOG, show us the SASLOG, by clicking on the {i} icon and pasting the SASLOG into the window that opens.

[SN1]

When i execute the code it is running perfectly.. but when i'm giving two values for state keyword the result is not as expected.

How should I correct it??

Requirement- 

selection criteria by State.  This should accommodate both “one state” as well as “multiple states”. You may define how the user specifies such a criterion in a reasonable way. If not specified then use all.    For example, user might specify this as 'CA' or 'CA','NY'

[ballardw]

---

@SN1 wrote:

When i execute the code it is running perfectly.. but when i'm giving two values for state keyword the result is not as expected.

How should I correct it??

Requirement- 

selection criteria by State.  This should accommodate both “one state” as well as “multiple states”. You may define how the user specifies such a criterion in a reasonable way. If not specified then use all.    For example, user might specify this as 'CA' or 'CA','NY'

---

The result of the call you show should generate an error about positional parameters.

Hint: commas inside of parameters are very bad

Second hint:

Does this look correct to you?

where (age <= 40 and year=2016) and state='CA','NY'; (It isn't).

[error_prone]

---

@SN1 wrote:
...

Requirement- 

selection criteria by State.  This should accommodate both “one state” as well as “multiple states”. You may define how the user specifies such a criterion in a reasonable way. If not specified then use all.    For example, user might specify this as 'CA' or 'CA','NY'

---

So you need to add code to the macro that creates a valid where statement incorporating that the user provides

• one state
• multiple states (don't just test with two states, try four)
• no state, so that all states appear in the report

And, of course, you have to re-think the way multiple values are passed to the macro.

[PaigeMiller]

---

@SN1 wrote:

When i execute the code it is running perfectly.. but when i'm giving two values for state keyword the result is not as expected.

How should I correct it??

---

This was never stated in the first message. You have to show us the problem. You have to provide enough details so we understand what the problem is.

So ... show us the problem, show us what you got and what you expected, so we can see what you are seeing.

[SN1]

I enhanced the code. when i executed the following code, its working fine if I did not give any values for 'state' but, the problem is if i give two values at a time for state (eg- i gave state='CA' or 'NY' , age=40, year=2016.) With the given values the output is showing the whole data in the dataset without filtering. If state= 'CA' and 'NY' the observations with 'CA' state are displayed excluding 'NY' values.

What i'm trying to do is i want a code which can handle both or multiple values or i can leave it like it's optional for 'state'.

the following code is working if i left it as optional..and if i give a single value but it is not showing the expected result. it is either taking all the observations or leaving the state value which is mentioned after 'and'.

for detailed requirement please refer to the pdf document which I have attatched. It has a mockup and details.

Any help is appreciated.

%macro profile (today=, state=, age=, year=);
proc report data=profile nowd colwidth=10 spacing=5 headline headskip;
column Acct_ID Name Age Balance Last_Tran_Date ;
define Last_Tran_Date / format=ddmmyy10.;
compute before;
line @16 "title: Detail Listing Of Account" @68 "Run Date: &today ";
line @16 "state: &state " @45 "Age: <= &age " @68 "Tran_year: &year ";
line " ";
endcomp;
compute after; 
line 60* "_";
line @40 'total balance ='
balance.sum dollar6.;
endcomp;%if %length(&state)>0 %then %do;
where (age <= &age and year=&year) and state=&state;
%end;%else %do;where age <= &age and year=&year;%end;
run;
%mend;

%profile (today= %sysfunc(today(),date9.),  age=40, year=2016);

[ballardw]

BEFORE coding a macro you need to know what the valid SAS syntax is for any of the cases where you use the parameters.

This statement indicates incorrect thinking as it is invalid SAS syntax.

 state='CA' or 'NY'

SAS supplies an operator that will allow to test if a variable is any one of multiple values: IN

state in ('CA' 'NY')  no commas needed so don't put them in a macro parameter.

The IN operator is also perfectly happy testing a single value

state in ('CA')

that should be a heavy enough clue stick though your state values must match exactly: "Ca" will not match "CA" so if there is doubt about the case of your variable you may consider:

upcase(state) in ('CA' 'NY')

The IN operator will also work with a list of numeric values such as

year in (2014 2015 2018)

[Tom · Accepted solution]

The main problem is your macro is generating this condition in the WHERE clause

state=&state

If you pass in a value of ('CA','NY') then your macro will generate this syntax

state=('CA','NY')

Which is invalid.

Change your code to use the IN operator and your call should work.

state in (&state)

Note that the extra () in your macro call is needed because you have a comma in the value. 

The extra () do not cause a problem for the IN operator.

But you could also just use a space instead of a comma between the values and then you would not need the () in the macro call.  The IN operator is just as happy to have a space delimited list as a comma delimited list.

%profile (today= %sysfunc(today(),date9.), state='CA' 'NY', age=40, year=2016);