Stored process puzzle 1

[Katie6]

Hi, I would like to be able to make a form which enables the user to run a query on our database of customer details. I have created a stored process which has three fields into which the user can enter details:
Field 1 = name,
Field 2 = ID
Field 3 = postcode.

If a member of staff enters the name 'John' into Field 1, I would like the stored process to bring up every customer in the database called John. But this could be hundreds of people, so I need the member of staff to be able to refine the search by entering the postcode and/or ID.

The member of staff will not always know all the details, and so it is important to be able to deal with empty fields. The code I have written so far is as below:

*************************************

/*Get the values entered by the user and call them keyed_name, keyed_ID and keyed_postcode respectively*/
%let keyed_name = &name;
%let keyed_ID = &keyed_id;
%let keyed_postcode = &postcode;

/*This deals with any empty fields and gives the macro variables keyed_name, keyed_ID and/or keyed_postcode the value of 'ne .' rather than missing. */

%macro logicstring;

/*if the name field is empty, then replace the name = missing with name = not missing*/
%if &name = "" %then %do;
keyed_name = 'ne .';
%end;

/*if the ID is missing, then replace the ID = missing with ID = not missing*/
%if &ID = "" %then %do;
keyed_ID = 'ne .';
%end;

/*if the postcode is missing, then replace the postcode = missing with name = not missing*/
%if &postcode = "" %then %do;
keyed_postcode = 'ne .';
%end;

%let separator = ' AND ';

%let x = 'name=keyed_name';
%let y = ID=keyed_ID';
%let z = 'postcode=keyed_postcode';

/*Join the strings x, y and z together and separate them with ' AND '.
This will produce the string 'name=keyed_name AND ID=keyed_ID AND postcode=keyed_postcode'*/
%let bigstring = catx(separator, x, y, z);


%put bigstring;

%mend;

%let %sysfunc (logicstring);

/*Print out the data in customer.customer_details where name=keyed_name AND ID=keyed_ID AND postcode=keyed_postcode.
If name was entered, the value of keyed_sortcode will be the value entered e.g. John. If it was left blank, SAS will print the values where
name is not equal to missing, in other words all of them.*/

proc print data=customer.customer_details
where(%logicstring);
run;

****************************************

I have only just started learning SAS for my new job, so some parts of this code may well be strange and wrong! Could any of you clever people explain why I have errors in my code and why this doesn't work. Any alternative suggestions would also be welcome. In the future, I may well introduce more than three fields.

Many thanks,

Katie

 

[shenniko]

Hi Katie,

I personally would do something similar to the below:-

%macro logicstring(	keyed_name=%str(),
			keyed_ID=%str(),
			keyed_postcode=%str());


Data Search_Table;
	Set customer.customer_details;

	%if &keyed_name. ne %str() %then
		%do;
			Where also Upcase(name) =Upcase("&keyed_name.");
		%end;
	%if &keyed_name. ne %str() %then
		%do;
			Where also Upcase(ID) =Upcase("&keyed_ID.");
		%end;
	%if &keyed_name. ne %str() %then
		%do;
			Where also Upcase(postcode) =Upcase("&keyed_postcode.");
		%end;
run;

proc print data=Search_Table;
run;

%mend logicstring;

And then use the example code below to run the macro:-

%logicstring(	keyed_name=john,
		keyed_ID=,
		keyed_postcode=);

or 

%logicstring(	keyed_name=john,
		keyed_ID=12345,
		keyed_postcode=);

Hope that helps,

Shenn

 

[Katie6]

Thanks Shenn!
I will try this out
Katie