topic Re: Proc Univariate help for a newbie in SAS Procedures

Proc Univariate help for a newbie

mikeed — Thu, 12 Apr 2018 18:06:49 GMT

I'm working with a very large dataset and it has a MONTH variable from 1-12,

and also a VALUE variable, with amounts possible for each month.

I would like to determine mean, median, etc. for a yearly (annual) amount,

summing each person's (observation) for 2 months of values.

What's the easiest way for a noob to do this?

Thanks in advance...

Re: Proc Univariate help for a newbie

PaigeMiller — Thu, 12 Apr 2018 18:10:25 GMT

I would like to determine mean, median, etc. for a yearly (annual) amount,

summing each person's (observation) for 2 months of values.

I''m afraid I'm not able to comprehend this part of the request. The top line makes perfect sense, but not in combination with the second line, which is rather cryptic.

Please show us a small amount of this data, and explain what results you'd like from this small amount of data.

Re: Proc Univariate help for a newbie

mikeed — Thu, 12 Apr 2018 18:20:32 GMT

sorry, typo. all 12 months.

each person has a unique ID and MONTHCODE variable from 1-12.

I want to sum the VALUEs of MONTHCODE1-12 for each person, then determine mean/median annual VALUE.

Re: Proc Univariate help for a newbie

PaigeMiller — Thu, 12 Apr 2018 18:24:19 GMT

I tend to use PROC SUMMARY for this, UNIVARIATE would also work but the code would be different

/* UNTESTED CODE */

proc summary data=have;
    class id;
    var value;
    output out=want mean=meanvalue median=medianvalue;
run;

Re: Proc Univariate help for a newbie

mikeed — Thu, 12 Apr 2018 18:23:51 GMT

ID MONTH VALUE

1 1 65

1 2 17

. . .

1 11 47

1 12 99

2 1 55

2 2 98

. . .

2 11 45

2 12 18

...

Re: Proc Univariate help for a newbie

mikeed — Thu, 12 Apr 2018 18:27:18 GMT

thanks,

would you be able to provide an example of the proc univariate code I would use?

Re: Proc Univariate help for a newbie

PaigeMiller — Thu, 12 Apr 2018 18:49:39 GMT

As I haven't used UNIVARIATE in years, my answer is that I can't, off the top of my head, provide UNIVARIATE code. It probably isn't much different, however you can read the documentation for PROC UNIVARIATE and see if you can figure it out.

The other problem is that UNIVARIATE is computing a huge amount of statistics that you haven't requested, and depending on much data you have, this could slow things down dramatically and produce a huge long output file.

Re: Proc Univariate help for a newbie

Reeza — Thu, 12 Apr 2018 19:39:38 GMT

Proc means, summary and univariate use similar processes so either should work for your request.

Why is PROC UNIVARIATE 'required'?

Here's a fully worked example of getting summary statistics using PROC MEAN:

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

Re: Proc Univariate help for a newbie

mikeed — Fri, 13 Apr 2018 12:27:52 GMT

I don't need to use Univariate, but I'm still having difficulty trying to figure out

how to determine the annual values of the summary statistics

Help still requested.

Re: Proc Univariate help for a newbie

PaigeMiller — Fri, 13 Apr 2018 14:11:42 GMT

@mikeed wrote:

I don't need to use Univariate, but I'm still having difficulty trying to figure out

how to determine the annual values of the summary statistics

Help still requested.

If the code I gave is not working properly for you, then please explain what is happening that is wrong, and show us the SASLOG and results. Otherwise, I assume the problem has been solved.

Re: Proc Univariate help for a newbie

ballardw — Fri, 13 Apr 2018 15:10:08 GMT

@mikeed wrote:

ID MONTH VALUE

1 1 65

1 2 17

. . .

1 11 47

1 12 99

2 1 55

2 2 98

. . .

2 11 45

2 12 18

3

...

NOW provide what the output is supposed to look like for that input.

Re: Proc Univariate help for a newbie

mikeed — Fri, 13 Apr 2018 15:16:16 GMT

For each ID, I'd would like to add up all the VALUES, from MONTH 1 to MONTH 12, for an annual VALUE.

Then I would like to find summary statistics of annual VALUE for each ID.

Perhaps this explains it better and it can't be accomplished in a single procedure, and that's my problem?

Re: Proc Univariate help for a newbie

PaigeMiller — Fri, 13 Apr 2018 16:59:14 GMT

@mikeed wrote:

For each ID, I'd would like to add up all the VALUES, from MONTH 1 to MONTH 12, for an annual VALUE.

Then I would like to find summary statistics of annual VALUE for each ID.

Perhaps this explains it better and it can't be accomplished in a single procedure, and that's my problem?

I provided code to do this in a single procedure already in the thread. Why do you discuss this as if it there is no such code?

Re: Proc Univariate help for a newbie

mikeed — Fri, 13 Apr 2018 17:51:33 GMT

I'm sorry, your code was not detailed enough and did not work since it did not help me

tally the MONTHs that I needed to find the statistics for.

Re: Proc Univariate help for a newbie

PaigeMiller — Fri, 13 Apr 2018 17:56:25 GMT

You can't just say "it didn't work". You have to give us details. You have to show us the SASLOG and the data set created, and explain why this is not the proper result.

Re: Proc Univariate help for a newbie

Reeza — Sat, 14 Apr 2018 18:31:52 GMT

@mikeed wrote:

For each ID, I'd would like to add up all the VALUES, from MONTH 1 to MONTH 12, for an annual VALUE.

Then I would like to find summary statistics of annual VALUE for each ID.

Perhaps this explains it better and it can't be accomplished in a single procedure, and that's my problem?

Yes, this is a two step problem so you can simply apply your proc twice.

The first time you're summing for the totals and in the second you're generating your summary statistics of the total value.

You can modify the statistics you get and the summary based on the statistics you specify in the PROC MEANS/SUMMARY statements.

You've been provided with multiple samples on how to run it for one, so you should be able to expand it to two sets of data.

But regardless, here's one way:

Re: Proc Univariate help for a newbie

PaigeMiller — Sat, 14 Apr 2018 21:23:56 GMT

But you don't need two runs of PROC MEANS or PROC SUMMARY to do this.

You can do it in a single run of PROC SUMMARY by removing the NWAY option, and then you get annual totals for each ID value, and the total across ALL ID values.

Re: Proc Univariate help for a newbie

Reeza — Sun, 15 Apr 2018 01:40:59 GMT

The summary statistics wouldn't reflect the annual total though, it would reflect the distribution of the monthly values, not the annual total.

@PaigeMiller wrote:

But you don't need two runs of PROC MEANS or PROC SUMMARY to do this.

You can do it in a single run of PROC SUMMARY by removing the NWAY option, and then you get annual totals for each ID value, and the total across ALL ID values.

Re: Proc Univariate help for a newbie

PaigeMiller — Sun, 15 Apr 2018 10:57:21 GMT

The mean when _TYPE_=0 is the mean of all the data. It is the annual total. It does not "reflect the distribution of monthly values".

Re: Proc Univariate help for a newbie

Reeza — Sun, 15 Apr 2018 17:59:27 GMT

It's the mean of the monthly values, not the annual values, so its on a different scale.

If you don't sum the monthly values first, TYPE=_0_ would still be on a monthly scale.

Original question:

I would like to determine mean, median, etc. for a yearly (annual) amount

I can only think of SQL that would enable it to run in a single step, but since you have a subquery it's not really a single step in my view.

/*Different solution, that does it all in one step or seems like it*/
proc sql;
create table want_one_step as
select mean(annual_total) as avg, median(annual_total) as med, std(annual_total) as std
from (select sum(value) as annual_total from have group by id);
quit;