BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cathy
Obsidian | Level 7

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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. 

Cathy
Obsidian | Level 7

I got the solution thanks.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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