|
Data Have ;
Input Y $8. Z $8. ;
Datalines ;
bbbbbbbb xxxxxxxx
dddddddd xxxxxxxx
ffffffff xxxxxxxx
hhhhhhhh yyyyyyyy
jjjjjjjj yyyyyyyy
;
Run ;
Proc SQL NoPrint ;
Select 'Select Quote(Y) Into : Var'||Substr( Z , 1 , 1 ) || ' Separated by "
, " From Have Where Z = "' || Z ||'" ;'
Into : Select Separated by " "
From Have ;
&Select
Quit ;
%Put VarX = &VarX
VarY = &VarY ;
Toby Dunn
To sensible men, every day is a day of reckoning. ~John W. Gardner
The important thing is this: To be able at any moment to sacrifice that
which we are for what we could become. ~Charles DuBois
Don't get your knickers in a knot. Nothing is solved and it just makes you
walk funny. ~Kathryn Carpenter
From: SAS_learner <proccontents@GMAIL.COM>
Reply-To: SAS_learner <proccontents@GMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Creating 1 macro variable for multiple rows in dataset
Date: Wed, 7 Feb 2007 16:45:44 -0600
How can this be done using SQL ?? Just curious to find out .
thanks
On 2/7/07, Howard Schreier <hs AT dc-sug DOT org> <nospam@howles.com> wrote:
>
>On Wed, 7 Feb 2007 20:11:23 -0000, Peter Crawford
><Peter.Crawford@BLUEYONDER.CO.UK> wrote:
>
> >I'm completely in agreement with Mr _null_
>
>We have a profound gender imbalance in this group, so until more
>information
>surfaces, can we assume it's Ms. Null?
>
> >
> >My posting was made to introduce the logical alternative to sql into
>:mVar
> >that is relevant when the information flows from processing in a data
>step.
> >
> >It needs to be appropriate, rather than large. I have no need or wish to
> >accumulate information from many rows in a single variable for the sole
> >purpose of loading a macro variable.
> >
> >Peter Crawford
> >Crawford Software Consultancy
> >UK
> >
> >
> >-----Original Message-----
> >From: data _null_; [mailto:datanull@gmail.com]
> >Sent: 07 February 2007 14:08
> >To: Peter Crawford
> >Cc: SAS-L@listserv.uga.edu
> >Subject: Re: Creating 1 macro variable for multiple rows in dataset
> >
> >No you don't have to worry about the 32K limit rather the 64K limit.
> >
> >5851 + ,"Judy"
> >ERROR: The text expression length (65538) exceeds maximum length
> >(65534). The text expression has
> > been truncated to 65534 characters.
> >
> >
> >Instead of figuring out ways to put more data into macro variables we
> >should be thinking about ways to keep data out of macro variables.
> >
> >We (the L) are always warning users about how bad it is to put data
> >into meta data but we are happy to show folks how to put more data in
> >more and more macro variables.
> >
> >I've seen code to put an entire data set into macro variables. While
> >I never say never there is surely a better way to accomplish whatever
> >the task may be, than taking data out of a data set and putting it
> >into macro variables.
> >
> >I asked the OP to tell us more about what he is trying to accomplish
> >but he has not seed fit to provide that information.
> >
> >I suspect that if the OP told us what he wanted to do with these
> >strings then there would be many "better" solutions that do not use
> >macro variables at all, and are more efficient.
> >
> >SAS has many more powerful tools than MACRO. The data step for one.
> >
> >
> >On 2/7/07, Peter Crawford <peter.crawford@blueyonder.co.uk> wrote:
> >> another datastep approach ~ which won't ru out of the 32K limit
> >> of data step variable widths
> >>
> >> This method uses call execute() to stack a %let statement;
> >>
> >> %let sourceD = sashelp.class; ** a simple demo dataset;
> >> %let sourceV = name ;
> >>
> >> %let target= ; *initialize your receiving macro variable;
> >> data _null_;
> >> call execute( '%nrstr(%%)let target= ' );
> >> do until( end_of_source );
> >> set &sourceD end= end_of_source ;
> >> call execute( ',' !! quote( trim( &sourceV )) ) ;
> >> *on principle, I always use function quote()
> >> to deal with any embedded quotes correctly ;
> >> end;
> >> call execute( ';' );
> >> stop;
> >> run;
> >> %let target = %substr( %superq(target), 2 ) ;
> >> *** removing initial comma;
> >>
> >> %let target = %sysfunc( translate( %superq(target)
> >> ,%str(%'),%str(%") ) );
> >> *** replacing double- with single-quotes;
> >>
> >> %let target = %sysfunc( tranwrd( %superq(target)
> >> ,%str( ,),%str(,) ) );
> >> *** removing 3 blanks preceeding each comma
> >>
> >> This has all been tested on windows-XP-pro.
> >> Your platform may implement differently, so beware!;
> >> ;
> >>
> >> Peter Crawford
> >>
> >>
> >>
> >> On Tue, 6 Feb 2007 15:34:10 -0500, Fehd, Ronald J. (CDC/CCHIS/NCPHI)
> >> <rjf2@CDC.GOV> wrote:
> >>
> >> >> From: Ross, Michael D
> >> >> I'm looking to create 1 macro variable containing "y" (rows
> >> >> 1-3), with single quotes around each value and commas
> >> >> seperating them for each instance of "z". So, I would have 2
> >> >> macro variable in the example below that would look like:
> >> >>
> >> >> &var1 = 'bbbbbbbb','dddddddd','ffffffff';
> >> >> &var2 = 'hhhhhhhh','iiiiiiii';
> >> >>
> >> >> Also, I know how to do this in Proc Sql - I was just
> >> >> wondering how to do it in the Data step.
> >> >
> >> >stick with sql;
> >> >you'll run out of space for your character variable
> >> >doing this in a data step
> >> >
> >> >DATA _Null_;
> >> >attrib Concatenation length = $ 200;
> >> >retain Concatenation ' ';
> >> >do until(EndoFile);
> >> > set Test end = EndoFile;
> >> > Concatenation = cat(Concatenation,y);
> >> > end;
> >> >call sumputX('var1',Concatenation);
> >> >stop;
> >> >run;
> >> >%Put Var1<&Var1.>;
> >> >
> >> >Ron Fehd the macro maven CDC Atlanta GA USA RJF2 at cdc dot gov
> >> >
> >> >--> cheerful provider of UNTESTED SAS code from the Clue?Gee!Wrx <--
> >> >
> >> >> data test;
> >> >> input y $8. z $8. ;
> >> >> datalines;
> >> >> bbbbbbbb xxxxxxxx
> >> >> dddddddd xxxxxxxx
> >> >> ffffffff xxxxxxxx
> >> >> hhhhhhhh yyyyyyyy
> >> >> jjjjjjjj yyyyyyyy
> >> >> ;
> >> >> run;
> >> >>
> >> >>
> >>
>
_________________________________________________________________
Invite your Hotmail contacts to join your friends list with Windows Live
Spaces
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us
|
