Date:         Thu, 3 Apr 2003 15:16:47 -0800
Reply-To:     "Huang, Ya" <yhuang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <yhuang@AMYLIN.COM>
Subject:      Re: sql, select into, preserve leading but not trailing blank?
Comments: To: "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Content-Type: text/plain; charset="iso-8859-1"

Thanks Harry, I like the first one better.

Best,

Ya

BTW, the double quoted "&alst" dose not seems to
be necessary, %sysfunc(compress(&alst,"'")) is OK.
Remember a similar question a while ago?

-----Original Message-----
From: Droogendyk, Harry [mailto:Harry.Droogendyk@CIBC.COM]
Sent: Thursday, April 03, 2003 3:02 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: sql, select into, preserve leading but not trailing blank?


Two ways:

1)      Wrap something around trim(a), then compress out the something:

data xx;
  a='abcdefg'; output;
  a='   kkkk'; output;
  a='ggg    '; output;
  a='mmmmmmm'; output;
run;

proc sql;
        select "'"||trim(a)||"'" into :alst separated by ' '
     from xx;
quit;

%let alst = %sysfunc(compress("&alst","'"));
%Put &alst;

2) Create separate macro variables and stick 'em together in a macro works.


data xx;
  a='abcdefg'; output;
  a='   kkkk'; output;
  a='ggg    '; output;
  a='mmmmmmm'; output;
run;

proc sql;
        select a into :a1 - :a999 notrim
     from xx;
quit;

%put _user_;

%macro a;
  %let alst = ;
  %do i = 1 %to &sqlobs;
     %let alst = &alst &&a&i;
  %end;
%mend a;

%a

%put &alst;

                -----Original Message-----
                From:   Huang, Ya [mailto:yhuang@AMYLIN.COM]
                Sent:   April 3, 2003 5:00 PM
                To:     SAS-L@LISTSERV.UGA.EDU
                Subject:        sql, select into, preserve leading but not
trailing blank?

                Hi there,

                424  data xx;
                425  a='abcdefg'; output;
                426  a='   kkkk'; output;
                427  a='ggg    '; output;
                428  a='mmmmmmm'; output;
                429

                NOTE: The data set WORK.XX has 4 observations and 1
variables.

                430  proc sql;
                431  select a into :alst separated by ' ' notrim
                432  from xx;
                433
                434  %put &alst;
                abcdefg    kkkk ggg     mmmmmmm

                In the above sample, I used notrim option, therefore,
                the leading blank for a='   kkkk' is preserved,
                but it also preserved the trailing blank for a='ggg    ',
                What I really want is to keep the leading blank, but
                to get rid of the trailing blank, so that &alst
                should look like:

                abcdefg    kkkk ggg mmmmmmm
                                   ^ no trailing blank here

                Any idea?

                Thanks

                Ya Huang