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

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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

Spoiler
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

Accepted Solutions
Solution
‎12-14-2015 01:13 PM
Trusted Advisor
Posts: 1,115

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

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;

 

View solution in original post


All Replies
Super User
Posts: 10,500

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

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;
Contributor
Posts: 40

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

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.

Super User
Posts: 10,500

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

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;
Contributor
Posts: 40

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

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.  

Solution
‎12-14-2015 01:13 PM
Trusted Advisor
Posts: 1,115

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

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;

 

Contributor
Posts: 40

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

That works FreelanceReinhard.  Thank you so much!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 360 views
  • 0 likes
  • 3 in conversation