SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

add data to each group

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

add data to each group

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 .

 


Accepted Solutions
Solution
‎12-10-2015 03:55 PM
Super User
Posts: 10,466

Re: add data to each group

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.

 

View solution in original post


All Replies
Super User
Posts: 10,466

Re: add data to each group

[ Edited ]

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;

 

Or

Proc append base=old data=new; Run;

to add records to the existing dataset.

Contributor
Posts: 33

Re: add data to each group

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

Re: add data to each group


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.

Super User
Posts: 17,745

Re: add data to each group

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

Re: add data to each group

Thanks Reeza! This works!!! Smiley Happy
Contributor
Posts: 33

Re: add data to each group

This is also a good and straightforward solution. Unfortunately, I can only accept one solution. But this is perfect!
Trusted Advisor
Posts: 1,607

Re: add data to each group

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

Re: add data to each group

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.
Solution
‎12-10-2015 03:55 PM
Super User
Posts: 10,466

Re: add data to each group

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.

 

Contributor
Posts: 33

Re: add data to each group

This works! Thanks!
Trusted Advisor
Posts: 1,607

Re: add data to each group


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.

Trusted Advisor
Posts: 1,115

Re: add data to each group

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;

 

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 579 views
  • 3 likes
  • 5 in conversation