DATA Step, Macro, Functions and more

how to merge proc means result with a new column

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 79
Accepted Solution

how to merge proc means result with a new column

Hello,

 

I want to merge the mean and std results only for y and x, created by proc means from dataset eq1, eq2, eq3. And add a new column with dataset name. 

Could anyone show me how to do it? 

Thanks

data eq1;
input ID y x z w;
cards;
1 1 1 27 40 
1 2 3 . 29 
1 3 5 30 . 
1 4 7 38 38 
2 1 1 23 45 
2 2 3 32 20 
2 3 5 67 . 
2 4 7 . 27 
3 1 1 33 23 
3 2 3 21 12 
3 3 5 78 . 
3 4 7 13 45 
4 1 1 56 45 
4 2 3 67 13 
4 3 5 . 35 
4 4 7 48 35 
;
run;
data eq2;
input ID y x z w;
cards;
1 1 27 40 8
1 2 . 29 37
1 3 30 . 25
1 4 38 38 23
2 1 23 45 19
2 2 32 20 .
2 3 67 . .
2 4 . 27 .
3 1 33 23 46
3 2 21 12 56
3 3 78 . 34
3 4 13 45 . 
4 1 56 45 23
4 2 67 13 67  
4 3 . 35 13
4 4 48 35 56 
;
run;

data eq3;
input y x ;
cards;
1 1
2 3
3 5
4 7
;
run;

Accepted Solutions
Solution
‎03-21-2018 12:45 PM
Esteemed Advisor
Posts: 5,611

Re: how to merge proc means result with a new column

Posted in reply to xiangpang

Use concatenate and merge:

 


data allEq;
length eqName $32;
set eq: indsname=nm;
eqName = scan(nm, 2);
run;

proc sort data=allEq; by eqName id; run;

proc means data=allEq noprint;
by eqName id;
var x y;
output out=allMeans mean= std= / autoname;
run;

data want;
merge allEq allMeans(drop=_type_ _freq_);
by eqName id;
run;
PG

View solution in original post


All Replies
Regular Contributor
Posts: 249

Re: how to merge proc means result with a new column

Posted in reply to xiangpang
What do you like your desired data output to look like?
Frequent Contributor
Posts: 79

Re: how to merge proc means result with a new column

13columns, 1 column with name of eq1-3, 4 columns for each dataset (2 for mean of y and z, 2 for std of yz)
3 rows, one row for the results from each dataset.
I also want to change the name of std to variance.

Thanks
Solution
‎03-21-2018 12:45 PM
Esteemed Advisor
Posts: 5,611

Re: how to merge proc means result with a new column

Posted in reply to xiangpang

Use concatenate and merge:

 


data allEq;
length eqName $32;
set eq: indsname=nm;
eqName = scan(nm, 2);
run;

proc sort data=allEq; by eqName id; run;

proc means data=allEq noprint;
by eqName id;
var x y;
output out=allMeans mean= std= / autoname;
run;

data want;
merge allEq allMeans(drop=_type_ _freq_);
by eqName id;
run;
PG
Frequent Contributor
Posts: 79

Re: how to merge proc means result with a new column

Thanks, it works. But I have two more questions for the alleq dataset part. For "set eq: indsname=nm;", does it work in case that I have data eq1, eq2, eq3 only? what if I have data eq1, eq2, eq3,eq11, eq21, eq31, but I only want the information from eq1, eq2, eq3?

 

another question for "eqName = scan(nm, 2);" why scan from the second word I think it should be "eqName = scan(nm, 1);". Is it because only one word, so there is no difference between 1 and 2 here?

 

Thanks again.

Super User
Posts: 23,928

Re: how to merge proc means result with a new column

Posted in reply to xiangpang

Note that the first step is combining the data sets back together. As indicated in previous posts this data structure will make the rest of your work problematic. 

 

Thanks, it works. But I have two more questions for the alleq dataset part. For "set eq: indsname=nm;", does it work in case that I have data eq1, eq2, eq3 only? what if I have data eq1, eq2, eq3,eq11, eq21, eq31, but I only want the information from eq1, eq2, eq3?

 

The colon is a prefix short cut, if you have a different naming structure you can use different methods for listing all data sets automatically. 

 

PREFIX: will select all data sets that start with PREFIX

D1-D20 will select all data sets labeled d1 to d20.

 

 

 

another question for "eqName = scan(nm, 2);" why scan from the second word I think it should be "eqName = scan(nm, 1);". Is it because only one word, so there is no difference between 1 and 2 here?

 

You can test this one yourself. Look at the value of NM and you'll see that it includes the library name. This makes sense because you could have data sets with the same name but different libraries such as:

 

set work.A perm.A;

Because it has the library name, the data set name is the 2 component in the SCAN() function.

 

 

Super User
Posts: 23,928

Re: how to merge proc means result with a new column

Posted in reply to xiangpang

Note that the first step is combining the data sets back together. As indicated in previous posts this data structure will make the rest of your work problematic. 

 

Thanks, it works. But I have two more questions for the alleq dataset part. For "set eq: indsname=nm;", does it work in case that I have data eq1, eq2, eq3 only? what if I have data eq1, eq2, eq3,eq11, eq21, eq31, but I only want the information from eq1, eq2, eq3?

 

The colon is a prefix short cut, if you have a different naming structure you can use different methods for listing all data sets automatically. 

 

PREFIX: will select all data sets that start with PREFIX

D1-D20 will select all data sets labeled d1 to d20.

 

 

 

another question for "eqName = scan(nm, 2);" why scan from the second word I think it should be "eqName = scan(nm, 1);". Is it because only one word, so there is no difference between 1 and 2 here?

 

You can test this one yourself. Look at the value of NM and you'll see that it includes the library name. This makes sense because you could have data sets with the same name but different libraries such as:

 

set work.A perm.A;

Because it has the library name, the data set name is the 2 component in the SCAN() function.

 

 

Super User
Posts: 23,928

Re: how to merge proc means result with a new column

Posted in reply to xiangpang

Note that the first step is combining the data sets back together. As indicated in previous posts this data structure will make the rest of your work problematic. 

 

Thanks, it works. But I have two more questions for the alleq dataset part. For "set eq: indsname=nm;", does it work in case that I have data eq1, eq2, eq3 only? what if I have data eq1, eq2, eq3,eq11, eq21, eq31, but I only want the information from eq1, eq2, eq3?

 

The colon is a prefix short cut, if you have a different naming structure you can use different methods for listing all data sets automatically. 

 

PREFIX: will select all data sets that start with PREFIX

D1-D20 will select all data sets labeled d1 to d20.

 

 

 

another question for "eqName = scan(nm, 2);" why scan from the second word I think it should be "eqName = scan(nm, 1);". Is it because only one word, so there is no difference between 1 and 2 here?

 

You can test this one yourself. Look at the value of NM and you'll see that it includes the library name. This makes sense because you could have data sets with the same name but different libraries such as:

 

set work.A perm.A;

Because it has the library name, the data set name is the 2 component in the SCAN() function.

 

 

Frequent Contributor
Posts: 79

Re: how to merge proc means result with a new column

Thanks, I am pleased to learn it. 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 197 views
  • 4 likes
  • 4 in conversation