BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

id
024
064
065
066
405


How to transform the above data to:


'024','064','065','066','405'

5 REPLIES 5
PaigeMiller
Diamond | Level 26

Here is a macro which I wrote which performs this transform ... it may or may not be overkill for what you need ... suggestions for improvement welcomed ... with regards to the original question, the user does not specify if ID is a data set variable or macro variable or text file or something else ... sure would be nice if that was specified ...

%macro put_quotes_around2(string=,delimiter=%str(,),input_delim=%str( ),
single_or_double=D)/DES='Put quotes around each word in a string, Paige Miller, TII (585) 784-5615';
/*************************************************************************/
/* Macro PUT_QUOTES_AROUND2                                              */
/* Paige Miller, Truesense Imaging, Inc. 12/20/13                        */
/* Purpose:                                                              */
/*   To take an input string of words/phrases, separated by a delimiter, */
/*   and take each word/phrase and put quotes around it and then         */
/*   separate the resulting words by a different delimiter. Most useful  */
/*   to construct text strings used in the IN clause of PROC SQL or the  */
/*   IN operator in a SAS data step.                                     */
/*                                                                       */
/*   This is an upgrade to %put_quotes_around1; in this macro we avoid   */
/*   using a global macro variable; instead the output of the macro is   */
/*   text, and one possible use of this macro is follows:                */
/*   %let mvar=%put_quotes_around2(...);                                 */
/*                                                                       */
/*   Will not work if &string has un-paired single or double quotes      */
/*                                                                       */
/*   Will not work if the resulting macro variable is longer than the    */
/*   maximum allowed length of a macro variable; in which case you       */
/*   could use CALL EXECUTE                                              */
/*                                                                       */
/* Example:                                                              */
/*   string=ABC\DEF GHI\FROG with input_delim=%str(\) results in an      */
/*   output string of 'ABC','DEF GHI','FROG'                             */
/*                                                                       */
/* Arguments:                                                            */
/*   STRING   Input text string, to be separated into components and     */
/*            each item quoted                                           */
/*   DELIMITER character used to delimit word/phrases in output string   */
/*            (default is comma)                                         */
/*   INPUT_DELIM character used to delimit word/phrases in input         */
/*            string (default is blank)                                  */
/*   SINGLE_OR_DOUBLE use S to indicate output string contains single    */
/*            quotes; use D to indicate output string contains double    */
/*            quotes; use N to indicate no quotes in output string. (D   */
/*            is default)                                                */
/*************************************************************************/

%local ii nwords newstring;
%let nwords=%word_count2(%superq(string),&input_delim);
%let newstring=;
%do ii=1 %to &nwords;
  %let thisword=%scan(%superq(string),&ii,&input_delim);
  %if %upcase(&single_or_double)=D %then %do;
   %let newstring=%unquote(&newstring.%str("&thisword"));
   %if &ii<&nwords %then %let newstring=&newstring.&delimiter;
  %end;
  %else %if %upcase(&single_or_double)=S %then %do;
   %let newstring=&newstring.%str(%')&thisword%str(%');
   %if &ii<&nwords %then %let newstring=&newstring.&delimiter;
  %end;
  %else %if %upcase(&single_or_double)=N %then %do;
   %let newstring=&newstring.&thisword;
   %if &ii<&nwords %then %let newstring=&newstring.&delimiter;
  %end;
%end;
%unquote(&newstring)
%mend;

/* Examples: */

/* Using macro as part of %let statement */

/*%let oink=%put_quotes_around2(string=X1 X2 X3,delimiter=%str(,),single_or_double=s);*/
/*%put OINK &oink;*/

/*%let oink=%put_quotes_around2(string=%quote(ABC\DEF GHI\DOG\M-21R),input_delim=\,delimiter=/);*/
/*%put OINK &oink;*/

/* Using macro without %let statement */

/*%let str=11 13 15;*/
/*data a;*/
/* set sashelp.class(where=(age in (%put_quotes_around2(string=%upcase(&str),single_or_double=N))));*/
/*run;*/

--
Paige Miller
Reeza
Super User

A bit simpler perhaps:

proc sql noprint;

select quote(trim(name)) into :list separated by ", "

from sashelp.class;

run;

%put &list.;

SASPhile
Quartz | Level 8

ok.I know it makes no difference, but how to insert sinlge quotes?

PaigeMiller
Diamond | Level 26

SASPhile wrote:

ok.I know it makes no difference, but how to insert sinlge quotes?

and earlier, Paige said:

with regards to the original question, the user does not specify if ID is a data set variable or macro variable or text file or something else ... sure would be nice if that was specified ...

and I don't see the answer

--
Paige Miller
Peter_C
Rhodochrosite | Level 12

I've got to agree with

When I have to do that macro thing, I usually end up using sysfunc()

%let sqs = %qsysfunc( translate( %superq(dquotes_string), %str(%'), %str(%") )) ;

as in

30 %let dquotes_string = "ert", "456789", "cvbn" ;

31

32  %let sqs = %qsysfunc( translate( %superq(dquotes_string), %str(%'), %str(%") )) ;

33

34  %put &sqs ;

'ert', '456789', 'cvbn'

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 803 views
  • 0 likes
  • 4 in conversation