How to add extra rows to a data set?

Accepted Solution Solved
Reply
Contributor krm
Contributor
Posts: 26
Accepted Solution

How to add extra rows to a data set?

[ Edited ]

Hi;

 

I have the data sample below:

 

data days;
input
id $1. days $5. count ;
datalines;
A day 1
A day 2
A day 3
B day 2
B day 5
B day 7
;

 

 For each ID I want to increase the count to maximum of 7 days. So For example in the the final data each ID would have 7 rows;

 

 

A day 1
A day 2
A day 3

A day 4

A day 5

A day 6

A day 7

B day 1
B day 2

B day 3

B day 4
B day 5

B day 6
B day 7

 

Thanks for the help.


Accepted Solutions
Solution
‎03-31-2016 10:55 AM
Super User
Posts: 10,028

Re: How to add extra rows to a data set?

It is easy for SQL.


data days;
input
id $1. days $5. count ;
datalines;
A day 1
A day 2
A day 3
B day 2
B day 5
B day 7
;
run;

proc summary data=days;
var count;
output out=temp(drop=_:) min=min max=max;
run;
data count;
 set temp;
 do count=min to max;
  output;
 end;
run;
proc sql;
create table want as
 select *
  from (select distinct id from days),
       (select distinct days from days),
       (select distinct count from count) 
    order by 1,2,3;
quit;


View solution in original post


All Replies
Super User
Posts: 19,810

Re: How to add extra rows to a data set?

Is this for reporting/presentation or do you need it in a dataset?

Contributor krm
Contributor
Posts: 26

Re: How to add extra rows to a data set?

I need it just in a dataset.
Super User
Posts: 11,343

Re: How to add extra rows to a data set?

Are there any other variables in the dataset? If so are the added rows supposed to contain any specific values.

For instance your ID value of A ends on day 3. Would the added rows have no values, the values for day 3 or something else.

Also Since your ID value of B starts with day 2 what values for additional variables would be assigned for day 1. And with the gap between day 2 and day 5 values would they have for days 3 and 4?

Solution
‎03-31-2016 10:55 AM
Super User
Posts: 10,028

Re: How to add extra rows to a data set?

It is easy for SQL.


data days;
input
id $1. days $5. count ;
datalines;
A day 1
A day 2
A day 3
B day 2
B day 5
B day 7
;
run;

proc summary data=days;
var count;
output out=temp(drop=_:) min=min max=max;
run;
data count;
 set temp;
 do count=min to max;
  output;
 end;
run;
proc sql;
create table want as
 select *
  from (select distinct id from days),
       (select distinct days from days),
       (select distinct count from count) 
    order by 1,2,3;
quit;


Super User
Posts: 7,782

Re: How to add extra rows to a data set?

data days;
input
id $1. days $5. count ;
datalines;
A day 1
A day 2
A day 3
B day 2
B day 5
B day 7
;
run;

data int;
set days;
by id;
if first.id;
keep id;
run;

data int2;
set int;
do count = 1 to 7;
  output;
end;
run;

data want;
merge
  days (in=a)
  int2 (in=b)
;
by id count;
if not a
then do;
  * set missing variables from days here;
  days = 'day';
end;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor krm
Contributor
Posts: 26

Re: How to add extra rows to a data set?

@KurtBremser and @Ksharp thank you for the solutions. I tried them both and they both worked fine. 

 

@ballardw, yes there are more variables in the dataset but I just wanted to keep it simple. The original data set has more than 200 ID variables and different years and weeks for each date. Also each day has certain transaction associated with it. 

 

 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 392 views
  • 2 likes
  • 5 in conversation