Date:         Wed, 12 Jan 2011 11:15:51 -0600
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: Transpose by SQL
Comments: To: Arthur Tabachneck <art297@rogers.com>
In-Reply-To:  <201101121713.p0CBmW8Y009652@wasabi.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

Ah, couldn't tell if that was just left-out spaces as elsewhere in the post
or not... thanks for noticing :)

-Joe

On Wed, Jan 12, 2011 at 11:13 AM, Arthur Tabachneck <art297@rogers.com>wrote:

> Joe,
>
> I came up with the same solution as your 'by hand' suggestion and am only
> posting it as it also concatenates pat and id like I thought were requested
> by the OP:
>
> proc sql;
>  create table want as
>     select catt(put(pat,3.),put(id,1.)) as patid,
>      max(case when name eq 'AE' then val else ' ' end) as val1,
>      max(case when name eq 'ME' then val else ' ' end) as val2,
>      max(case when name eq 'DD' then val else ' ' end) as val3
>        from have
>          group by calculated patid
>  ;
> quit;
>
> Art
> ---------
> On Wed, 12 Jan 2011 10:55:10 -0600, Joe Matise <snoopy369@GMAIL.COM>
> wrote:
>
> >It's certainly possible to do, but PROC TRANSPOSE is just very very good
> at
> >doing what it does :)
> >
> >If I were to do it in PROC SQL, the approach I'd use is to pull the names
> >into a macro list and use MAX and GROUP BY to get at them.  Something like
> >this (first hard-coded, then with macro):
> >
> >data have;
> >input pat id name $ val $;
> >cards;
> >101 1 AE AAA
> >101 1 ME BBBB
> >101 1 DD CC
> >101 2 AE XXXXX
> >101 2 ME YY
> >101 2 DD ZZZZZZ
> >;
> >run;
> >
> >
> >**This is what we are creating, if we wrote it by hand;
> >proc sql;
> >create table want as
> >    select pat, id, max(case when name='AE' then val else ' ' end) as AE,
> >            max(case when name='ME' then val else ' ' end) as ME,
> >            max(case when name='DD' then val else ' ' end) as DD
> >from have group by pat, id;
> >quit;
> >
> >*macro to replace the repeated max part;
> >%macro max_var(var);
> >max(case when name="&var." then val else ' ' end) as &var
> >%mend max_var;
> >
> >proc sql;
> >*select the names into a list of macro calls;
> >select distinct cats('%max_var(',name,')') into :varlist separated by ','
> >from have;
> >*actually create table;
> >create table want as
> >    select pat, id, &varlist from have group by pat, id;
> >quit;
> >
> >
> >
> >On Wed, Jan 12, 2011 at 10:49 AM, Steve Steve <steve11145@yahoo.com>
> wrote:
> >
> >> *Thanks for your reply.*
> >>
> >> * *
> >>
> >> *I use SQL in my program from the start to the end except using proc
> >> transpose in the middle. I think that there may be a way to do the
> transpose
> >> by SQL, but do not know how. Just wondering.*
> >>
> >>  ------------------------------
> >> *From:* Joe Matise <snoopy369@GMAIL.COM>
> >> *To:* SAS-L@LISTSERV.UGA.EDU
> >> *Sent:* Wed, January 12, 2011 11:35:53 AM
> >> *Subject:* Re: Transpose by SQL
> >>
> >> Why do you want to use SQL?  SAS PROC TRANSPOSE will do that trivially
> (use
> >> ID=name).
> >>
> >> If you're in a class and being taught how to do transpose by SQL, I
> suggest
> >> you write us back with how you'd approach it and/or where you're stuck,
> and
> >> we can give you more productive guidance.
> >>
> >> -Joe
> >>
> >> On Wed, Jan 12, 2011 at 10:23 AM, Steve Steve <steve11145@yahoo.com>
> >> wrote:
> >>
> >> > How to do the following by SQL, please?
> >> > Thanks.
> >> >
> >> >
> >> > datahave;
> >> > inputpat id name $ val $;
> >> > cards;
> >> > 101 1 AE AAA
> >> > 101 1 ME BBBB
> >> > 101 1 DD CC
> >> > 101 2 AE XXXXX
> >> > 101 2 ME YY
> >> > 101 2 DD ZZZZZZ
> >> > ;
> >> >
> >> > Ideal output
> >> > Patid AE ME DD
> >> > 1011  AAA BBBB CC
> >> > 1012  XXXXX YY ZZZZZZ
> >> >
> >> >
> >> >
> >> >
> >>
> >>
>