topic Re: Prompt - How to handle commas in user entered list of values in SAS Programming

Prompt - How to handle commas in user entered list of values

KelseyB — Mon, 14 Dec 2015 17:22:58 GMT

Hello,
I am hoping someone can help me with the following. I have created a prompt within Enterprise Guide that accepts user-entered text as a single value. The user will enter a list of numbers or text separated by commas. I want to capture the list of values and then replace the commas with single quote comma single quote.

For example:
User enters 1,2,3, and it is stored in macro variable DRGFilterCommas.
DRGCommas_List is created and stores '1','2','3'

I do not want to allow the user to enter multiple values (I have tried that already), but the user could enter 30 values and entering one at a time is cumbersome as well.

Here is the code I have so far:

data _null_;
DRGCommas_temp=tranwrd(%str(&DRGFilterCommas),",","','");
DRGCommas_List=cat("'",trim(DRGCommas_temp),"'");
run;

Here is the error I am getting:

ERROR 72-185: The TRANWRD function call has too many arguments.

Any ideas on how to do this are greatly appreciated.

Thank you,
KelseyB

Re: Prompt - How to handle commas in user entered list of values

ballardw — Mon, 14 Dec 2015 17:37:12 GMT

Try adding

comma= "','";

and use the variable instead of "','"; Too many quotes.

I suspect that it might be easier to use a space separated list unless you are using this in multiple different contexts.


data example;
   x = '1 2 3 4 5';
   length DRGCommas_temp $ 125;
   DRGCommas_temp='';
   do i= 1 to (countw(x));
      DRGCommas_temp= catx(',',DRGCommas_temp,quote(scan(x,i)));
   end;
run;

Re: Prompt - How to handle commas in user entered list of values

KelseyB — Mon, 14 Dec 2015 17:45:11 GMT

Thanks ballardw. I can't delimit with a space because the values could have spaces in them , for example, Joe Smith, Suzie Jones. etc.

I tried to substitute the comma as you suggested and got the same error unfortunately.

data _null_;
length DRGsCommas_List $10000;
/*Replace comma with single quote comma single quote and then concatenate single quote at beginning and end of list of DRGs*/
comma = ",";
DRGCommas_temp=tranwrd(&DRGFilterCommas,comma,'comma');
DRGCommas_List=cat("'",trim(DRGCommas_temp),"'");
run;

ERROR 72-185: The TRANWRD function call has too many arguments.

Re: Prompt - How to handle commas in user entered list of values

FreelanceReinh — Mon, 14 Dec 2015 18:04:20 GMT

How about this?

%let DRGFilterCommas=1,2,3; 

data _null_;
length DRGCommas_List $10000; 
DRGCommas_List=cats("'", tranwrd("&DRGFilterCommas", ",", "','"), "'");
put DRGCommas_List; /* just for checking */
run;

Re: Prompt - How to handle commas in user entered list of values

KelseyB — Mon, 14 Dec 2015 18:13:48 GMT

That works FreelanceReinhard. Thank you so much!

Re: Prompt - How to handle commas in user entered list of values

ballardw — Mon, 14 Dec 2015 18:16:28 GMT

SCAN and COUNTW allow specifying the delimiter.


data example;
   x = 'Sue Jones, Fred Smith, Billy Bog';
   length DRGCommas_temp $ 1250;
   DRGCommas_temp='';
   do i= 1 to (countw(x,','));
      DRGCommas_temp= catx(',',DRGCommas_temp,quote(scan(x,i,',')));
   end;
run;

Re: Prompt - How to handle commas in user entered list of values

KelseyB — Mon, 14 Dec 2015 18:18:49 GMT

Thanks ballardw. I will check this way out also. I received a solution from another post, but I will check this method out as well.