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

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 .

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
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

12 REPLIES 12
ballardw
Super User

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.

statz
Obsidian | Level 7
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.
ballardw
Super User

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

Reeza
Super User

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;
statz
Obsidian | Level 7
Thanks Reeza! This works!!! 🙂
statz
Obsidian | Level 7
This is also a good and straightforward solution. Unfortunately, I can only accept one solution. But this is perfect!
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
statz
Obsidian | Level 7
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.
ballardw
Super User
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.

 

statz
Obsidian | Level 7
This works! Thanks!
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
FreelanceReinh
Jade | Level 19

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;

 

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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