topic Re: add data to each group in SAS Data Management

add data to each group

statz — Thu, 10 Dec 2015 17:46:08 GMT

Dear all,

I have two existing datasets called old and new. Old consists of the variables Months, group, and responseY. The dataset "new" consists of only one variable - Months. Can someone please help me create a dataset which contains the dataset "old" and also the "new" data repeated for every group? i tried many ways such as append, merge, set, but they did not result to my target dataset. Thanks in advance.

For example, here is the data "old":

data old;

input Months group $ responseY;

1 A 1.2

2 A 1.3

3 A 1.4

1 B 1.1

2 B 1.2

3 B 1.3

;

run;

here is data "new":

data new(drop=ii);
do ii=0 to 10/0.1; Months=0.1*ii; output;end;
run;

I would like to obtain this dataset:

Months	group	responseY
1	A	1.2
2	A	1.3
3	A	1.4
1	B	1.1
2	B	1.2
3	B	1.3
0	A	.
0.1	A	.
0.2	A	.
0.3	A	.
0.4	A	.
0.5	A	.
0.6	A	.
0.7	A	.
0.8	A	.
0.9	A	.
1	A	.
1.1	A	.
1.2	A	.
1.3	A	.
1.4	A	.
1.5	A	.
1.6	A	.
1.7	A	.
1.8	A	.
1.9	A	.
2	A	.
2.1	A	.
2.2	A	.
2.3	A	.
2.4	A	.
2.5	A	.
2.6	A	.
2.7	A	.
2.8	A	.
2.9	A	.
3	A	.
3.1	A	.
3.2	A	.
3.3	A	.
3.4	A	.
3.5	A	.
3.6	A	.
3.7	A	.
3.8	A	.
3.9	A	.
4	A	.
4.1	A	.
4.2	A	.
4.3	A	.
4.4	A	.
4.5	A	.
4.6	A	.
4.7	A	.
4.8	A	.
4.9	A	.
5	A	.
5.1	A	.
5.2	A	.
5.3	A	.
5.4	A	.
5.5	A	.
5.6	A	.
5.7	A	.
5.8	A	.
5.9	A	.
6	A	.
6.1	A	.
6.2	A	.
6.3	A	.
6.4	A	.
6.5	A	.
6.6	A	.
6.7	A	.
6.8	A	.
6.9	A	.
7	A	.
7.1	A	.
7.2	A	.
7.3	A	.
7.4	A	.
7.5	A	.
7.6	A	.
7.7	A	.
7.8	A	.
7.9	A	.
8	A	.
8.1	A	.
8.2	A	.
8.3	A	.
8.4	A	.
8.5	A	.
8.6	A	.
8.7	A	.
8.8	A	.
8.9	A	.
9	A	.
9.1	A	.
9.2	A	.
9.3	A	.
9.4	A	.
9.5	A	.
9.6	A	.
9.7	A	.
9.8	A	.
9.9	A	.
10	A	.
0	B	.
0.1	B	.
0.2	B	.
0.3	B	.
0.4	B	.
0.5	B	.
0.6	B	.
0.7	B	.
0.8	B	.
0.9	B	.
1	B	.
1.1	B	.
1.2	B	.
1.3	B	.
1.4	B	.
1.5	B	.
1.6	B	.
1.7	B	.
1.8	B	.
1.9	B	.
2	B	.
2.1	B	.
2.2	B	.
2.3	B	.
2.4	B	.
2.5	B	.
2.6	B	.
2.7	B	.
2.8	B	.
2.9	B	.
3	B	.
3.1	B	.
3.2	B	.
3.3	B	.
3.4	B	.
3.5	B	.
3.6	B	.
3.7	B	.
3.8	B	.
3.9	B	.
4	B	.
4.1	B	.
4.2	B	.
4.3	B	.
4.4	B	.
4.5	B	.
4.6	B	.
4.7	B	.
4.8	B	.
4.9	B	.
5	B	.
5.1	B	.
5.2	B	.
5.3	B	.
5.4	B	.
5.5	B	.
5.6	B	.
5.7	B	.
5.8	B	.
5.9	B	.
6	B	.
6.1	B	.
6.2	B	.
6.3	B	.
6.4	B	.
6.5	B	.
6.6	B	.
6.7	B	.
6.8	B	.
6.9	B	.
7	B	.
7.1	B	.
7.2	B	.
7.3	B	.
7.4	B	.
7.5	B	.
7.6	B	.
7.7	B	.
7.8	B	.
7.9	B	.
8	B	.
8.1	B	.
8.2	B	.
8.3	B	.
8.4	B	.
8.5	B	.
8.6	B	.
8.7	B	.
8.8	B	.
8.9	B	.
9	B	.
9.1	B	.
9.2	B	.
9.3	B	.
9.4	B	.
9.5	B	.
9.6	B	.
9.7	B	.
9.8	B	.
9.9	B	.
10	B	.

Re: add data to each group

ballardw — Thu, 10 Dec 2015 20:26:29 GMT

You might want your NEW data set to be made as:

data new(drop=ii);

length group $ 1; /* the length here should match that of Group in the OLD data set*/

do group = 'A', 'B';
do ii=0 to 10/0.1;

Months=0.1*ii;

output;

end;

end;
run;

To create new combined dataset.

Data want;

Set old new;

run;

Proc append base=old data=new; Run;

to add records to the existing dataset.

Re: add data to each group

PaigeMiller — Thu, 10 Dec 2015 18:16:33 GMT

data new(drop=ii);
do ii=0 to 10/0.1; Months=0.1*ii;group='A'; output; group='B'; output; end;
run;

data old;
input Months group $ responseY;
cards;
1 A 1.2
2 A 1.3
3 A 1.4
1 B 1.1
2 B 1.2
3 B 1.3
;
run;

proc sql;
	create table want as select n.months,n.group,o.responseY from new as n left join old as o on n.months=o.months and n.group=o.group;
quit;

Re: add data to each group

statz — Thu, 10 Dec 2015 19:29:24 GMT

Thank you Paige. However, for the old data, we do not know what are the different values of group. We only know that the old data has the variable group but no information on the values. Thanks.

Re: add data to each group

statz — Thu, 10 Dec 2015 19:29:41 GMT

Hi ballardw,

For the first solution, it is not possible because when we create the target dataset, we do not actually know (pretend to not know) what are the group names in the existing data.
The second and third solution would not repeat the month values to each group level.

Thanks.

Re: add data to each group

ballardw — Thu, 10 Dec 2015 20:24:04 GMT

proc sql;
   select distinct quote(strip(group)) into : groups separated by ','
   from old;
quit;

data new(drop=ii);
   length group $ 1;  /* the length here should match that of Group in the OLD data set*/
   do group = &Groups;
      do ii=0 to 10/0.1;
      Months=0.1*ii;
      output;
      end;
   end;
run;

If you don't know the values then you can get them. If there are thousands of values then this may not work.

Re: add data to each group

ballardw — Thu, 10 Dec 2015 20:25:48 GMT

@statz wrote:
Hi ballardw,

For the first solution, it is not possible because when we create the target dataset, we do not actually know (pretend to not know) what are the group names in the existing data.
The second and third solution would not repeat the month values to each group level.

Thanks.

You did realize that I was referencing that different NEW data set that I recommended, not your old version missing the groups.

Re: add data to each group

PaigeMiller — Thu, 10 Dec 2015 20:26:58 GMT

@statz wrote:
Thank you Paige. However, for the old data, we do not know what are the different values of group. We only know that the old data has the variable group but no information on the values. Thanks.

It sure would be helpful if you had stated that at the beginning so we wouldn't waste our time coming up with solutions that aren't really solutions.

I see @ballardw has offered you a solution, I hope that works for you.

Re: add data to each group

Reeza — Thu, 10 Dec 2015 20:27:04 GMT

Create an intermediary table by using your OLD as a starting point.

proc sql;
create table temp as
select distinct o_x.group, n.Months 
from old as o_x
cross join
new as n;
quit;

data want;
set old temp;
run;

Re: add data to each group

statz — Thu, 10 Dec 2015 20:54:06 GMT

Thanks Reeza! This works!!! 🙂

Re: add data to each group

statz — Thu, 10 Dec 2015 20:55:06 GMT

This works! Thanks!

Re: add data to each group

statz — Thu, 10 Dec 2015 20:57:15 GMT

This is also a good and straightforward solution. Unfortunately, I can only accept one solution. But this is perfect!

Re: add data to each group

FreelanceReinh — Sat, 12 Dec 2015 00:06:42 GMT

Hello @statz,

Just a side note: If you want your Months values to behave as you would expect them to, I would strongly recommend to define them using the round function or as ii/10 rather than 0.1*ii. Otherwise, many of them will be "contaminated" with numeric representation issues. The following data step shows what I mean:

data _null_;
ii=3;
Months=0.1*ii;
if Months ne 0.3 then put 'Not equal!';
Months=round(0.1*ii, 0.1); /* Rounding unit 0.1 is suitable because ii is integer, but, e.g., 1E-9 would do as well. */
if Months=0.3 then put 'Now, after rounding, they are equal!';
Months=ii/10;
if Months=0.3 then put 'Using only integers in the calculation is fine, too.';
run;