DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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

 

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


Accepted Solutions
Solution
‎04-21-2016 11:30 PM
Super User
Posts: 19,772

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

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. 

View solution in original post


All Replies
Solution
‎04-21-2016 11:30 PM
Super User
Posts: 19,772

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

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. 

Contributor
Posts: 37

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

I got the solution thanks.

Super User
Super User
Posts: 7,942

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

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 240 views
  • 1 like
  • 3 in conversation