|
The PROC SUMMARY solution, though concise and neatly constructed, doesn't "declare" requirements for a solution as directly as does the SQL program. I suspect that Tammie will find a problem with the missing values that result from her statement of the problem. If so, the SQL solution makes it easy to see that non-key attributes are being maximized over groups of distinct values of temp1,value1. In the dark ages before SAS PROC SQL (BCQL), I used PROC SUMMARY extensively and found the class statement (nway) a great help. Now I find extensions of SQL well worth a slight increase in the burden of syntax. For example, SQL syntax highlights the fact that the program maximizes the non-key values independently within groups, while the SUMMARY syntax doesn't.
S
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of ./ ADD NAME=Data _null_;
Sent: Wednesday, March 11, 2009 7:25 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: little (more) help with proc sql
SQL might be considered a MultiMaster (as in "I need to remove grout, sand in the corners of an antique wardrobe, and cut nails"), but it looks a bit clumsy for data summary. Consider an alternative PROC SUMMARY. Yes I know the question was "more SQL help", but we're using SAS and there is no reason to ignore that.
data ab / view=ab;
set a b;
run;
proc summary data=ab nway;
class temp1 value2;
output out=concise(drop=_:) max(height weight position origin)=;
run;
On 3/10/09, Sigurd Hermansen <HERMANS1@westat.com> wrote:
> Tammie:
> Ah so.... then let's specify that set of constraints as
>
> data a;input temp1 value2 height weight;
> datalines;
> 100222 5 180 80
> 100222 6 190 90
> ;;;;
> run;
> data b;input temp1 value2 position origin;
> datalines;
> 100222 7 1 5
> 100222 6 1 5
> 100222 7 2 5
> 999222 7 2 5
> ;;;;
> run;
> proc sql;
> create table test as
> select distinct coalesce(t1.temp1,t2.temp1) as temp1,
> coalesce(t1.value2,t2.value2) as value2,
> max(height) as height,
> max(weight) as weight,
> max(position) as position,
> max(origin) as origin
> from a as t1 full join b as t2
> on t1.temp1=t2.temp1 and t1.value2=t2.value2
> group by calculated temp1,calculated value2
> ;
> quit;
>
> From the results of this query, given your sample data, we can see
> that some distinct combinations of temp1 and value2 in the a and b
> datasets have no related values of height and weight or of value and
> position. Perhaps you have another relation among temp1 and value2 and
> the other variables in mind. S
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> tammie
> Sent: Tuesday, March 10, 2009 8:20 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: little (more) help with proc sql
>
>
> Sigurd Hermansen wrote
> > Are you attempting to join weight and height to position and origin
> > by ID and to coalesce Value2 (keep non-missing values from one or
> > the other dataset)? If you can define your objective more precisely,
> > a SQL solution may involve nothing more than specifying your
> > requirements.
>
> I would like to get each distinct value for temp1 and value2 and bring
> in the largest values (non missing) for all other variables
>
|
