DATA Step, Macro, Functions and more

Combine the results of proc means

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Combine the results of proc means

 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_8hihi_lo
01057N10571057105710571057105710571057105710571057
01057MIN-0.22111-0.2219-0.17418-0.17602-0.16021-0.17463-0.15269-0.17586-0.18802-0.21369-0.26344
01057MAX0.481140.3731230.3782190.4077850.3690880.4376920.456960.4825420.4656720.5658490.191825
01057MEAN0.0055210.008180.0093930.0103070.010910.0118860.0125130.0133770.0140610.0164130.010892
01057STD0.0627280.0516460.0470110.0447210.0424430.0421080.0430490.0451130.0473270.0580480.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. 


Accepted Solutions
Solution
‎02-01-2017 09:47 PM
Super User
Super User
Posts: 7,401

Re: Combine the results of proc means

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-dat...

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;

View solution in original post


All Replies
Solution
‎02-01-2017 09:47 PM
Super User
Super User
Posts: 7,401

Re: Combine the results of proc means

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-dat...

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;
Contributor
Posts: 39

Re: Combine the results of proc means

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. 

Super User
Super User
Posts: 7,401

Re: Combine the results of proc means

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.

Contributor
Posts: 39

Re: Combine the results of proc means

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

Id.PNG

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,

idpama.PNG

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

Valued Guide
Posts: 797

Re: Combine the results of proc means

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.

Contributor
Posts: 39

Re: Combine the results of proc means

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.
Super User
Posts: 10,500

Re: Combine the results of proc means


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.

 

Contributor
Posts: 39

Re: Combine the results of proc means

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 184 views
  • 7 likes
  • 4 in conversation