Transform

SASPhile — Thu, 06 Mar 2014 20:06:54 GMT

id
024
064
065
066
405

How to transform the above data to:

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

Re: Transform

PaigeMiller — Thu, 06 Mar 2014 20:27:39 GMT

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

Re: Transform

Reeza — Thu, 06 Mar 2014 21:01:42 GMT

A bit simpler perhaps:

proc sql noprint;

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

from sashelp.class;

run;

%put &list.;

Re: Transform

SASPhile — Thu, 06 Mar 2014 21:12:14 GMT

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

Re: Transform

PaigeMiller — Fri, 07 Mar 2014 13:13:06 GMT

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

Re: Transform

Peter_C — Fri, 07 Mar 2014 17:38:39 GMT

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" ;

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

34 %put &sqs ;

'ert', '456789', 'cvbn'

topic Re: Transform in SAS Programming

Transform

Re: Transform

Re: Transform

Re: Transform

Re: Transform

Re: Transform