Prev: Formatting Output
Next: How would I create a program to do a repetitive process?
From: rangoonraja123 on 14 Feb 2007 10:27
Hi All,

How are you doing. Happy valentine day.

Question?
i am getting following in log note: The query requires remerging
summary statistics back with the original data

when i run below code
proc sql;
create table length as
select distinct ptno,visit, max(length) as maxlt
from physical.length
where visit in (1,2)
group by ptno
order by study, ptno;
quit;

Every ptno has two visits. the intention is to pick the maximum value
length of those two visits.
for example

ptno visit length
001 1 5
001 2 6
002 1 8
002 2 3

i want the output to be
ptno visit length
001 2 6
002 1 8 and as in the original prog i don't want to
create variable maxlt. i want the attributes of original variable
legnth.
i can write the code like max(length) as length but then it will lose
its attributes let's say
label = (length in cms) format 8. etc and i don't want to add the
label statement in the code.
i want it to automatically pull from the original dataset that is "
physical.length" as done in data step.


Thank you all.
Regards,
Raju.

From: Arthur Tabachneck on 14 Feb 2007 13:37
Raju,

Of course you can do it in SQL. The note you were getting simply
states what is happening.

Does the following accomplish what you want?

data have;
input study ptno $ @13 visit 1.0 @16 length 8.0;
label length='length in cm'
visit='visit #';
cards;
1 001 1 5
1 001 2 6
1 002 1 8
1 002 2 3
2 001 1 5
2 001 2 6
2 002 1 8
2 002 2 3
;
run;

proc sql;
create table length as
select distinct *
from have
group by study,ptno
having length=max(length)
and visit in (1,2)
order by study, ptno;
quit;

Art
----------
On Feb 14, 10:27 am, rangoonraja...(a)gmail.com wrote:
> Hi All,
>
> How are you doing. Happy valentine day.
>
> Question?
> i am getting following in log note: The query requires remerging
> summary statistics back with the original data
>
> when i run below code
> proc sql;
> create table length as
> select distinct ptno,visit, max(length) as maxlt
> from physical.length
> where visit in (1,2)
> group by ptno
> order by study, ptno;
> quit;
>
> Every ptno has two visits. the intention is to pick the maximum value
> length of those two visits.
> for example
>
> ptno visit length
> 001 1 5
> 001 2 6
> 002 1 8
> 002 2 3
>
> i want the output to be
> ptno visit length
> 001 2 6
> 002 1 8 and as in the original prog i don't want to
> create variable maxlt. i want the attributes of original variable
> legnth.
> i can write the code like max(length) as length but then it will lose
> its attributes let's say
> label = (length in cms) format 8. etc and i don't want to add the
> label statement in the code.
> i want it to automatically pull from the original dataset that is "
> physical.length" as done in data step.
>
> Thank you all.
> Regards,
> Raju.


From: "Howard Schreier hs AT dc-sug DOT org>" on 14 Feb 2007 16:14
On Feb 14, 10:27 am, rangoonraja...(a)gmail.com wrote:
> Hi All,
>
> How are you doing. Happy valentine day.
>
> Question?
> i am getting following in log note: The query requires remerging
> summary statistics back with the original data
>
> when i run below code
> proc sql;
> create table length as
> select distinct ptno,visit, max(length) as maxlt
> from physical.length
> where visit in (1,2)
> group by ptno
> order by study, ptno;
> quit;
>
> Every ptno has two visits. the intention is to pick the maximum value
> length of those two visits.
> for example
>
> ptno visit length
> 001 1 5
> 001 2 6
> 002 1 8
> 002 2 3
>
> i want the output to be
> ptno visit length
> 001 2 6
> 002 1 8 and as in the original prog i don't want to
> create variable maxlt. i want the attributes of original variable
> legnth.
> i can write the code like max(length) as length but then it will lose
> its attributes let's say
> label = (length in cms) format 8. etc and i don't want to add the
> label statement in the code.
> i want it to automatically pull from the original dataset that is "
> physical.length" as done in data step.
>
> Thank you all.
> Regards,
> Raju.

It akes two statements, one to establish the new table and the other
to populate it.

Try this:

data physical_length;
input
ptno visit length; label length = 'Visit Length';
cards;
001 1 5
001 2 6
002 1 8
002 2 3
;

proc sql;

create table length like physical_length;

insert into length (ptno, length)
select ptno, max(length)
from physical_length
group by ptno;

select * from length;

quit;

The output:

Visit
ptno visit Length
----------------------------
1 . 6
2 . 8

Notice that the label is inherited by the new table from the old.

From: Arthur Tabachneck on 14 Feb 2007 16:40
Howard,

While I was originally thinking that one would first have to create
the table using a like statement, I was surprised to find that the
labels came across without it.

Do you know why that worked?

Art

p.s. the code I used was:

data have;
input study ptno $ @13 visit 1.0 @16 length 8.0;
label length='length in cm'
visit='visit #';
cards;
1 001 1 5
1 001 2 6
1 002 1 8
1 002 2 3
2 001 1 5
2 001 2 6
2 002 1 8
2 002 2 3
;
run;


proc sql;
create table length as
select distinct *
from have
group by study,ptno
having length=max(length)
and visit in (1,2)
order by study, ptno;
quit;

------------
On Feb 14, 4:14 pm, "Howard Schreier <hs AT dc-sug DOT org>"
<nos...(a)howles.com> wrote:
> On Feb 14, 10:27 am, rangoonraja...(a)gmail.com wrote:
>
>
>
>
>
> > Hi All,
>
> > How are you doing. Happy valentine day.
>
> > Question?
> > i am getting following in log note: The query requires remerging
> > summary statistics back with the original data
>
> > when i run below code
> > proc sql;
> > create table length as
> > select distinct ptno,visit, max(length) as maxlt
> > from physical.length
> > where visit in (1,2)
> > group by ptno
> > order by study, ptno;
> > quit;
>
> > Every ptno has two visits. the intention is to pick the maximum value
> > length of those two visits.
> > for example
>
> > ptno visit length
> > 001 1 5
> > 001 2 6
> > 002 1 8
> > 002 2 3
>
> > i want the output to be
> > ptno visit length
> > 001 2 6
> > 002 1 8 and as in the original prog i don't want to
> > create variable maxlt. i want the attributes of original variable
> > legnth.
> > i can write the code like max(length) as length but then it will lose
> > its attributes let's say
> > label = (length in cms) format 8. etc and i don't want to add the
> > label statement in the code.
> > i want it to automatically pull from the original dataset that is "
> > physical.length" as done in data step.
>
> > Thank you all.
> > Regards,
> > Raju.
>
> It akes two statements, one to establish the new table and the other
> to populate it.
>
> Try this:
>
> data physical_length;
> input
> ptno visit length; label length = 'Visit Length';
> cards;
> 001 1 5
> 001 2 6
> 002 1 8
> 002 2 3
> ;
>
> proc sql;
>
> create table length like physical_length;
>
> insert into length (ptno, length)
> select ptno, max(length)
> from physical_length
> group by ptno;
>
> select * from length;
>
> quit;
>
> The output:
>
> Visit
> ptno visit Length
> ----------------------------
> 1 . 6
> 2 . 8
>
> Notice that the label is inherited by the new table from the old.- Hide quoted text -
>
> - Show quoted text -


From: "Howard Schreier hs AT dc-sug DOT org>" on 14 Feb 2007 22:18
On Feb 14, 4:40 pm, "Arthur Tabachneck" <art...(a)netscape.net> wrote:
> Howard,
>
> While I was originally thinking that one would first have to create
> the table using a like statement, I was surprised to find that the
> labels came across without it.
>
> Do you know why that worked?
>
> Art
>
> p.s. the code I used was:
>
> data have;
> input study ptno $ @13 visit 1.0 @16 length 8.0;
> label length='length in cm'
> visit='visit #';
> cards;
> 1 001 1 5
> 1 001 2 6
> 1 002 1 8
> 1 002 2 3
> 2 001 1 5
> 2 001 2 6
> 2 002 1 8
> 2 002 2 3
> ;
> run;
>
> proc sql;
> create table length as
> select distinct *
> from have
> group by study,ptno
> having length=max(length)
> and visit in (1,2)
> order by study, ptno;
> quit;
>
> ------------
> On Feb 14, 4:14 pm, "Howard Schreier <hs AT dc-sug DOT org>"
>
> <nos...(a)howles.com> wrote:
> > On Feb 14, 10:27 am, rangoonraja...(a)gmail.com wrote:
>
> > > Hi All,
>
> > > How are you doing. Happy valentine day.
>
> > > Question?
> > > i am getting following in log note: The query requires remerging
> > > summary statistics back with the original data
>
> > > when i run below code
> > > proc sql;
> > > create table length as
> > > select distinct ptno,visit, max(length) as maxlt
> > > from physical.length
> > > where visit in (1,2)
> > > group by ptno
> > > order by study, ptno;
> > > quit;
>
> > > Every ptno has two visits. the intention is to pick the maximum value
> > > length of those two visits.
> > > for example
>
> > > ptno visit length
> > > 001 1 5
> > > 001 2 6
> > > 002 1 8
> > > 002 2 3
>
> > > i want the output to be
> > > ptno visit length
> > > 001 2 6
> > > 002 1 8 and as in the original prog i don't want to
> > > create variable maxlt. i want the attributes of original variable
> > > legnth.
> > > i can write the code like max(length) as length but then it will lose
> > > its attributes let's say
> > > label = (length in cms) format 8. etc and i don't want to add the
> > > label statement in the code.
> > > i want it to automatically pull from the original dataset that is "
> > > physical.length" as done in data step.
>
> > > Thank you all.
> > > Regards,
> > > Raju.
>
> > It akes two statements, one to establish the new table and the other
> > to populate it.
>
> > Try this:
>
> > data physical_length;
> > input
> > ptno visit length; label length = 'Visit Length';
> > cards;
> > 001 1 5
> > 001 2 6
> > 002 1 8
> > 002 2 3
> > ;
>
> > proc sql;
>
> > create table length like physical_length;
>
> > insert into length (ptno, length)
> > select ptno, max(length)
> > from physical_length
> > group by ptno;
>
> > select * from length;
>
> > quit;
>
> > The output:
>
> > Visit
> > ptno visit Length
> > ----------------------------
> > 1 . 6
> > 2 . 8
>
> > Notice that the label is inherited by the new table from the old.- Hide quoted text -
>
> > - Show quoted text -

I did not read the problem carefully enough, and Raju was worrying
about column attributes before getting the actual content right. It
turns out that solving the content problem (using Art's code) takes
care of the attribute problem.

Art; The key is that your code carries the column LENGTH from the
source table to the output; attributes are inherited. On the other
hand, when a new column is created from an expression (like
MAX(LENGTH) ), it starts with default attributes.

 |  Next  |  Last
Pages: 1 2
Prev: Formatting Output
Next: How would I create a program to do a repetitive process?