topic Re: Combine the results of proc means in SAS Programming

Combine the results of proc means

yotsuba88 — Mon, 30 Jan 2017 11:04:31 GMT

Hi guys,

I have datasets but I cannot figure out how to combine them into table for my study.

Data 1: Id

_TYPE_	_FREQ_	_STAT_	lo	_1	_2	_3	_4	_5	_6	_7	_8	hi	hi_lo
0	1057	N	1057	1057	1057	1057	1057	1057	1057	1057	1057	1057	1057
0	1057	MIN	-0.22111	-0.2219	-0.17418	-0.17602	-0.16021	-0.17463	-0.15269	-0.17586	-0.18802	-0.21369	-0.26344
0	1057	MAX	0.48114	0.373123	0.378219	0.407785	0.369088	0.437692	0.45696	0.482542	0.465672	0.565849	0.191825
0	1057	MEAN	0.005521	0.00818	0.009393	0.010307	0.01091	0.011886	0.012513	0.013377	0.014061	0.016413	0.010892
0	1057	STD	0.062728	0.051646	0.047011	0.044721	0.042443	0.042108	0.043049	0.045113	0.047327	0.058048	0.032986

Data 2 : residual (the same table but different data)

I want to keep MEAN and change it to ID or RESIDUAL in horizons. And keep columns lo _1 _2 _3...

Please tell me how can I do it. Thank you so much.

Re: Combine the results of proc means

RW9 — Mon, 30 Jan 2017 11:14:10 GMT

Post your test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

And provide what you want the output to look like. Your post is all messed up across the screen and can't see it.

It sounds like a simple set:

data want;
  set data1 (where=(_stat_="MEAN") keep=_1 _2 _3 in=a)
       data2 (where=(_stat_="MEAN") keep=_1 _2 _3 in=b);
  if a then _stat_="RESIDUAL";
  else _stat_="ID";
run;

Re: Combine the results of proc means

yotsuba88 — Mon, 30 Jan 2017 13:10:31 GMT

Thank you so much, I will pay attention to fix my post now.

How about if I want to insert t-value next to mean value by change it to * or **. ?

I tried to find and it seems I must use macro to run this.

Re: Combine the results of proc means

RW9 — Mon, 30 Jan 2017 14:23:42 GMT

Hi,

Please post test data, in the form of a datastep and what you want out. Your original post doesn't even show a t-test, but a similar process can work, just change the if's, and the where to include t-test.

Also note: "I tried to find and it seems I must use macro to run this. "

That statement is completely untrue. Macro does nothing at all, it is just a code generator, it does nothing itself. Your task is a very simple one, and one datastep should do it.

Re: Combine the results of proc means

yotsuba88 — Mon, 30 Jan 2017 16:26:58 GMT

Hi,

I really appreciate your reply. I tried to follow the instruction to insert data but I need time to figure out step 2 and 3. So I insert the photos here to show my data.

1. First, I want to combine 2 tables (as photo 1), I think your code can solve it, but when I run it , there are errors:

ERROR: Variable _STAT_ is not on file R.ID_MEAN.
ERROR: Variable _STAT_ is not on file R.JUMP_MEAN.

Photo 1: Id or residual

2. I want to add tvalue and prob next to hi_lo column in photo 1 and show prob like *. And I only found macro code for this, maybe I am wrong in searching codes.

Photo 2: hi_lo,

I am a new one in SAS so sorry if my request is so stupid. Thank you.

Re: Combine the results of proc means

mkeintz — Mon, 30 Jan 2017 19:44:32 GMT

And your latest input on the topic is further illustration of why a SAS data set is crucial in solving the problem. You show a picture of a data set with the variable _STAT_, and you show error messages from a sas log claiming _stat_ is not in the identified data sets. We would be in a far better position to help if the actual data, or a sample thereof were in the topic presentation. At the very least show us the log in which the error message occured. Either the vars aren't in the data sets, or your program has a component the eliminates access to those vars.

Re: Combine the results of proc means

ballardw — Tue, 31 Jan 2017 16:58:17 GMT

@yotsuba88 wrote:

Hi,

I really appreciate your reply. I tried to follow the instruction to insert data but I need time to figure out step 2 and 3. So I insert the photos here to show my data.

1. First, I want to combine 2 tables (as photo 1), I think your code can solve it, but when I run it , there are errors:

ERROR: Variable _STAT_ is not on file R.ID_MEAN.
ERROR: Variable _STAT_ is not on file R.JUMP_MEAN.

When you get an ERROR: Show the code with the error message from the log. Copy and paste into a code box in the forum using the {i} icon.

Run proc contents on your data sets:

proc contents data=R.id_mean;
run;

Do you actually see a variable _stat_ in the results?

How does "Photo1: id or residual" relate to R.id_mean or R.Jump_mean? "Id or residual" is not a typical SAS data set name.

Similar what relationship does "Photo 2: hi_lo" have to those two named data sets? And since the Photo 1 has a variable hi_lo did the lables to your photos get mixed up?

And what would the result of the combination look like? When you combine a data set with one row with another consisting of multiple rows there a number of options that all depend on the desired result. Do you want the shown value of prob on each row or only the first?

And when you say you want prob like * you need to provide a rule and all of the possible values for assignment especially if you are using that ancient social science convention of * ** *** or **** to mean "more significant" which is wrong in so many ways.

You can create a custom format to use with value ranges to display anything based on a range:

Proc Format library=work;
value Myvalue
0    - .15 = 'small'
.15 <- .5  = 'middle'
.5  <- .99 = 'larger'
.99 <- high= 'largest'
;
run;

And then for example in proc print add a statement of: Format prob myvalue. ; to display prob using the text for the given range.

Re: Combine the results of proc means

yotsuba88 — Thu, 02 Feb 2017 02:46:48 GMT

Thank you so much. I am learning to post sample in the forum. Anw, I can solve the problem a bit, except the combine tvalue and prob.

Re: Combine the results of proc means

yotsuba88 — Thu, 02 Feb 2017 02:53:30 GMT

Thank you so much for your detail explanation. Sorry but I dont understand the meaning of prob like *, I need to check again.

Photo 1, I mean there are 2 tables like pho 1 (id and residual), and I want to merge 2 tables in one.
Photo 2, the estimate is "hi_lo", and I want to merge tvalue and prob next to hi_lo into photo 1.
Anw, I can solve the problem in the table 1, but not for adding tvalue and prob next to hi_lo.