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

 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. 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
yotsuba88
Quartz | Level 8

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

yotsuba88
Quartz | Level 8

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. 

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
yotsuba88
Quartz | Level 8
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.
ballardw
Super User

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

 

yotsuba88
Quartz | Level 8
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.

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
  • 8 replies
  • 2151 views
  • 7 likes
  • 4 in conversation