turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculating average age

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-02-2016 06:15 PM

Hello Everyone,

Below dataset shows the automobile sales for each individual and the duration of time they spent using those vehicles. I want to create a table which shows number of individuals who bought 1 vehicle, 2 vehicles, 3 vehicles etc.. Also, I need to know average age of these individuals, %males, %females and average duration these individuals spent using those vehicles. Below is the sample dataset.

Customer_ID | sales | age | gender | sdate | edate |

1 | car | 12 | M | 1/1/2001 | 1/12/2001 |

1 | bike | 12 | M | 1/2/2001 | 1/18/2001 |

1 | truck | 14 | M | 1/6/2003 | 1/8/2003 |

2 | car | 22 | F | 3/4/2001 | 3/8/2001 |

3 | bike | 34 | M | 2/4/2002 | 2/12/2002 |

3 | bike | 34 | M | 2/10/2002 | 2/24/2002 |

3 | truck | 35 | M | 2/14/2003 | 2/18/2003 |

6 | bike | 74 | F | 3/15/2003 | 3/18/2003 |

4 | car | 40 | M | 3/15/2003 | 3/18/2003 |

4 | truck | 41 | M | 3/20/2004 | 3/26/2004 |

5 | bike | 32 | F | 3/23/2001 | 3/29/2004 |

My output should look something like below. First column represent; number of vehicels sold . Second Column represents, how many such individuals bought these vehicles. Example: There was 1 sale for 3 subject IDs (ID-2,6,5), 2 sales for only one ID (ID-4) etc. Now I need find average age of individuals, %males, %females and average duration these individuals used these vehicles.

No of vehicles | No of Individuals | Avg Age (Mean, SD) | %males | %females | Avg duration (Mean, SD) |

1 | 3 | ||||

2 | 1 | ||||

3 | 2 |

The average age of first row in the above table should be: (22+74+32)/3. But, there is an outlier 74 in this age. What is the best way to calculate average age if there is an outlier. Moreover, the third row has two individuals who had three sales. So, will the average age be (12+12+14+34+34+35)/6. How should I calculate. Guide me.

Thank you in advance!

Accepted Solutions

Solution

10-02-2016
11:57 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to danwarags

10-02-2016 09:53 PM

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to danwarags

10-02-2016 06:46 PM

Majority of your requirements are either proc means or proc freq.

For outliers in your average look at the TRIMMED MEAN in proc means.

http://support.sas.com/training/tutorial/

Look at the Summary Statistics and Descriptive Statistics videos. Right hand side of the page.

Solution

10-02-2016
11:57 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to danwarags

10-02-2016 09:53 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

10-03-2016 12:20 AM - edited 10-03-2016 12:21 AM

Thank you so much Ksharp. This was the exact output I needed. But, the only problem is the output dataset shows higher standard deviation for age, which means that there are outliers that needs to be eliminated. I have never used proc robustreg before. I am a physician by profession, so have less stats background. Could you please guide me eliminating outliers in the below dataset.

ID | sales | age |

1 | car | 12 |

1 | bike | 12 |

1 | truck | 14 |

2 | car | 22 |

3 | bike | 34 |

3 | bike | 34 |

3 | truck | 35 |

6 | bike | 74 |

4 | car | 40 |

4 | truck | 41 |

5 | bike | 32 |

7 | car | 34 |

7 | bike | 35 |

7 | plane | 36 |

7 | truck | 37 |

8 | bike | 72 |

8 | car | 73 |

8 | plane | 73 |

8 | truck | 74 |

After eliminating outliers my output should look as below:

No of vehicles | No of IDs | Avg Age | std Age |

1 | 3 | ||

2 | 1 | ||

3 | 2 | ||

4 | 2 |

I really appreciate your help. Thank you in advance.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to danwarags

10-03-2016 03:40 AM

You didn't offer enough data . Apply the following code, Open dataset OUTLIERS. data have; infile cards expandtabs truncover; input Customer_ID sales $ age gender $ (sdate edate) (: mmddyy10.); format sdate edate mmddyy10.; cards; 1 car 12 M 1/1/2001 1/12/2001 1 bike 12 M 1/2/2001 1/18/2001 1 truck 14 M 1/6/2003 1/8/2003 2 car 22 F 3/4/2001 3/8/2001 3 bike 34 M 2/4/2002 2/12/2002 3 bike 34 M 2/10/2002 2/24/2002 3 truck 35 M 2/14/2003 2/18/2003 6 bike 74 F 3/15/2003 3/18/2003 4 car 40 M 3/15/2003 3/18/2003 4 truck 41 M 3/20/2004 3/26/2004 5 bike 32 F 3/23/2001 3/29/2004 ; run; proc robustreg data=have method=lts; model age = ; output out=outliers outlier=outliers; run;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to danwarags

10-03-2016 03:49 AM

Or IML code. data have; infile cards expandtabs truncover; input Customer_ID sales $ age gender $ (sdate edate) (: mmddyy10.); format sdate edate mmddyy10.; cards; 1 car 12 M 1/1/2001 1/12/2001 1 bike 12 M 1/2/2001 1/18/2001 1 truck 14 M 1/6/2003 1/8/2003 2 car 22 F 3/4/2001 3/8/2001 3 bike 34 M 2/4/2002 2/12/2002 3 bike 34 M 2/10/2002 2/24/2002 3 truck 35 M 2/14/2003 2/18/2003 6 bike 74 F 3/15/2003 3/18/2003 4 car 40 M 3/15/2003 3/18/2003 4 truck 41 M 3/20/2004 3/26/2004 5 bike 32 F 3/23/2001 3/29/2004 ; run; proc iml; use have; read all var{age}; close; optn = j(9,1,.); call lms(scLMS, coefLMS, wgtLMS, optn, age); call lts(scLTS, coefLTS, wgtLTS, optn, age); LMSOutliers = loc(wgtLMS[1,]=0); LTSOutliers = loc(wgtLTS[1,]=0); print LMSOutliers, LTSOutliers; quit; OUPTUT: ( the number of obs) LMSOutliers 1 2 3 8 LTSOutliers 1 2 3 8

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to danwarags

10-03-2016 04:26 AM

proc robustreg data=have; model age=/ cutoff=1; output out=outliers outlier=outlier; run; proc print;run;