topic Re: Urgent help need for selecting min and max value using proc sql in SAS Programming

Urgent help need for selecting min and max value using proc sql

Cathy — Fri, 22 Apr 2016 02:01:28 GMT

I have data set for demo. where I want to create the avearge percent of weight and height and getting the table containg only 4 observations where min of weight, max of weight and min of height and max of height. But my coding is giving me column of same min and max every observation...Please kindly help me again. Thanks.

below is just an example of table that I want.

Ht	Wt	Percent of Average Ht	Percent of Average Wt
155	200	87.1	….
145	150	….	….
171	220	….	….
141	110	….	….

Here is the coding what I have done so far;

proc sql;

create table demonew as

select *,

mean(ht) as mean_ht format=6.2,

(ht/mean(ht)) as pct_total_ht format=Percent8.4,

mean(wt) as mean_wt format=6.2,

(ht/mean(wt)) as pct_total_wt format=Percent8.4,

max(ht) as max_ht,

min(wt) as min_wt

from demo

;

select *

from demonew

;

quit;

That's what I get with above code.

Ht	Wt	Percent of Average Ht	Percent of Average Wt	Max Wt	Max Ht
158	190	87.1	….	220	171
146	140	….	….	220	171
161	210	….	….	220	171
151	130	….	….	220	171

Re: Urgent help need for selecting min and max value using proc sql

Reeza — Fri, 22 Apr 2016 02:42:30 GMT

If there's a column you don't want, drop it from the select statement.

It looks like you can just drop the last two vars from your select statement unless I'm missing something.

Re: Urgent help need for selecting min and max value using proc sql

Cathy — Fri, 22 Apr 2016 03:30:01 GMT

I got the solution thanks.

Re: Urgent help need for selecting min and max value using proc sql

RW9 — Fri, 22 Apr 2016 08:30:26 GMT

What is the actual question here? It seems to me there are several things in your code. Lets step through it. Your required output appears to HT and WT, so from that I would guess that you want a distinct list of each WT/HT variant, and sums based on that grouping. If thats not the case then why are these here, what are they min and max perhaps? Its not clear. Secondly you select columns you want in the output tables, so if you don't want something, don't select it. Thirdly you are apply SAS formats to the columns. Whilst this may make it look like the value is X.YY, it doesn't change the underlying value. For any output or QC dataset you are better off specifying the rounding in your spec, then applying round() function to the value - that way you and your QC'er will have the same value, otherwise you might have 45.1234 in your data, but format it as 41.12, and the QC'er actually has 45.12 which are not equal.

The last SQL step doesn't appear to do anything?

Finally, here is code to create what you want, note formatted consitently, indented etc. for ease of readability (and corrected some typos like HT in the WT calculation):

proc sql;
  create table DEMONEW as
  select  distinct
          HT,
          WT,
          round(HT/mean(HT),6.1) as PCT_TOTAL_HT,    
          round(WT/mean(WT),6.1) as PCT_TOTAL_WT
  from    DEMO
  group by HT,
           WT;
quit;