DATA Step, Macro, Functions and more

Transform

Reply
Super Contributor
Posts: 647

Transform

id
024
064
065
066
405


How to transform the above data to:


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

Trusted Advisor
Posts: 1,611

Re: Transform

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;*/

Super User
Posts: 17,795

Re: Transform

A bit simpler perhaps:

proc sql noprint;

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

from sashelp.class;

run;

%put &list.;

Super Contributor
Posts: 647

Re: Transform

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

Trusted Advisor
Posts: 1,611

Re: Transform

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

Valued Guide
Posts: 2,174

Re: Transform

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'

Ask a Question
Discussion stats
  • 5 replies
  • 238 views
  • 0 likes
  • 4 in conversation