proc sql note and attributes. [SAS]

Prev: Formatting Output
Next: How would I create a program to do a repetitive process?

From: rangoonraja123 on 15 Feb 2007 10:00

On Feb 14, 10:18 pm, "Howard Schreier <hs AT dc-sug DOT org>"
<nos...(a)howles.com> wrote:
> 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.

Hi Art and Howard,
Art, with your code i am getting the attributes but not able
to achieve what i wanted.
(i was getting only ptno 003)
Howard, with your code i was able to get attributes but the
output is what i got from my code.

I think i was not able to explain to you my problem fully in my
previous mail. I will give you an example of the original dataset and
the output i wanted.
(note: if you could achieve the output even without visit it is also
fine. see outputs below)
i could achieve the output in datastep but i want to do in sql

ORIGINAL DATASET;( i have more variables in my original dataset then
shown here, but i am keeping ptno, visit and length or ptno and
length, anything is fine)

PTNO VISIT LENGTH

001 1.00 68.8
001 2.00 68.8
001 998.00 70.4
001 5.00 69.0
002 1.00 74.6
002 2.00 74.6
002 6.00 75.4
002 31.00 75.3
002 41.00 76.5
002 51.00 76.9
002 61.00 75.8
002 71.00 76.2
002 81.00 75.7
002 91.00 75.0
002 101.00 75.8
002 998.00 75.2
002 10.00 .
002 5.00 75.1
002 7.00 .
002 9.00 74.7
002 32.00 75.6
002 42.00 76.3
002 52.00 76.8
002 62.00 76.8
003 1.00 84.7
003 2.00 84.3
003 6.00 81.1
003 998.00 80.9
003 10.00 .
003 5.00 81.0
003 7.00 .
003 9.00 79.0

OUTPUT DATASET NEEDED:

PTNO VISIT LENGTH

001 2.00 68.8
002 2.00 74.6
003 1.00 84.7

if you could achieve even this kind of output also fine.

PTNO LENGTH

001 68.8
002 74.6
003 84.7

I appreciate your help
Thank you
Raju.

From: "Howard Schreier hs AT dc-sug DOT org>" on 15 Feb 2007 12:21

On Feb 15, 10:00 am, rangoonraja...(a)gmail.com wrote:
> On Feb 14, 10:18 pm, "Howard Schreier <hs AT dc-sug DOT org>"
>
>
>
> <nos...(a)howles.com> wrote:
> > 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.
>
> Hi Art and Howard,
> Art, with your code i am getting the attributes but not able
> to achieve what i wanted.
> (i was getting only ptno 003)
> Howard, with your code i was able to get attributes but the
> output is what i got from my code.
>
> I think i was not able to explain to you my problem fully in my
> previous mail. I will give you an example of the original dataset and
> the output i wanted.
> (note: if you could achieve the output even without visit it is also
> fine. see outputs below)
> i could achieve the output in datastep but i want to do in sql
>
> ORIGINAL DATASET;( i have more variables in my original dataset then
> shown here, but i am keeping ptno, visit and length or ptno and
> length, anything is fine)
>
> PTNO VISIT LENGTH
>
> 001 1.00 68.8
> 001 2.00 68.8
> 001 998.00 70.4
> 001 5.00 69.0
> 002 1.00 74.6
> 002 2.00 74.6
> 002 6.00 75.4
> 002 31.00 75.3
> 002 41.00 76.5
> 002 51.00 76.9
> 002 61.00 75.8
> 002 71.00 76.2
> 002 81.00 75.7
> 002 91.00 75.0
> 002 101.00 75.8
> 002 998.00 75.2
> 002 10.00 .
> 002 5.00 75.1
> 002 7.00 .
> 002 9.00 74.7
> 002 32.00 75.6
> 002 42.00 76.3
> 002 52.00 76.8
> 002 62.00 76.8
> 003 1.00 84.7
> 003 2.00 84.3
> 003 6.00 81.1
> 003 998.00 80.9
> 003 10.00 .
> 003 5.00 81.0
> 003 7.00 .
> 003 9.00 79.0
>
> OUTPUT DATASET NEEDED:
>
> PTNO VISIT LENGTH
>
> 001 2.00 68.8
> 002 2.00 74.6
> 003 1.00 84.7
>
> if you could achieve even this kind of output also fine.
>
> PTNO LENGTH
>
> 001 68.8
> 002 74.6
> 003 84.7
>
> I appreciate your help
> Thank you
> Raju.

From: "Howard Schreier hs AT dc-sug DOT org>" on 15 Feb 2007 12:23

From: rangoonraja123 on 15 Feb 2007 13:51

On Feb 15, 12:23 pm, "Howard Schreier <hs AT dc-sug DOT org>"
<nos...(a)howles.com> wrote:
> On Feb 15, 10:00 am, rangoonraja...(a)gmail.com wrote:
>
>
>
> > On Feb 14, 10:18 pm, "Howard Schreier <hs AT dc-sug DOT org>"
>
> > <nos...(a)howles.com> wrote:
> > > 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;
>
> > > >procsql;
> > > > 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 lognote: The query requires remerging
> > > > > > summary statistics back with the original data
>
> > > > > > when i run below code
> > > > > >procsql;
> > > > > > 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
> > > > > ;
>
> > > > > procsql;
>
> > > > > 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.
>
> > Hi Art and Howard,
> > Art, with your code i am getting the attributes but not able
> > to achieve what i wanted.
> > (i was getting only ptno 003)
> > Howard, with your code i was able to get attributes but the
> > output is what i got from my code.
>
> > I think i was not able to explain to you my problem fully in my
> > previous mail. I will give you an example of the original dataset and
> > the output i wanted.
> > (note: if you could achieve the output even without visit it is also
> > fine. see outputs below)
> > i could achieve the output in datastep but i want to do insql
>
> > ORIGINAL DATASET;( i have more variables in my original dataset then
> > shown here, but i am keeping ptno, visit and length or ptno and
> > length, anything is fine)
>
> > PTNO VISIT LENGTH
>
> > 001 1.00 68.8
> > 001 2.00 68.8
> > 001 998.00 70.4
> > 001 5.00 69.0
> > 002 1.00 74.6
> > 002 2.00 74.6
> > 002 6.00 75.4
> > 002 31.00 75.3
> > 002 41.00 76.5
> > 002 51.00 76.9
> > 002 61.00 75.8
> > 002 71.00 76.2
> > 002 81.00 75.7
> > 002 91.00 75.0
> > 002 101.00 75.8
> > 002 998.00 75.2
> > 002 10.00 .
> > 002 5.00 75.1
> > 002 7.00 .
> > 002 9.00 74.7
> > 002 32.00 75.6
> > 002 42.00 76.3
> > 002 52.00 76.8
> > 002 62.00 76.8
> > 003 1.00 84.7
> > 003 2.00 84.3
> > 003 6.00 81.1
> > 003 998.00 80.9
> > 003 10.00 .
> > 003 5.00 81.0
> > 003 7.00 .
> > 003 9.00 79.0
>
> > OUTPUT DATASET NEEDED:
>
> > PTNO VISIT LENGTH
>
> > 001 2.00 68.8
> > 002 2.00 74.6
> > 003 1.00 84.7
>
> > if you could achieve even this kind of output also fine.
>
> > PTNO LENGTH
>
> > 001 68.8
> > 002 74.6
> > 003 84.7
>
> > I appreciate your help
> > Thank you
> > Raju.
>
> I presume that the unresolved issue is breaking the tie when you have
> the same LENGTH value for both VISIT values.
>
> An inline view is needed. Try
>
> procsql;
> create table length as
> select *
> from ( select distinct *
> from physical_length
> where visit in (1,2)
> group by ptno
> having length=max(length)
> )
> group by ptno
> having visit=max(visit)
> ;
> quit;

Hi ,

Thank. the above code worked.

Regards,
Raju.

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