## Expand rows

Solved
Frequent Contributor
Posts: 82

# Expand rows

[ Edited ]

We have two datasets:

``````/* dataset A */
Period              num_1   num_2   num_3
4/2001 - 6/2001     62       14      35``````
``````/* dataset B */
Time_Period
1/2001 - 3/2001
4/2001 - 6/2001
7/2001 - 9/2001
10/2001 - 12/2001``````

The output dataset would look like:

``````/* dataset want */
Time_Period         num_1 num_2 num_3
1/2001 - 3/2001      0     0     0
4/2001 - 6/2001      62    14    35
7/2001 - 9/2001      0     0     0
10/2001 - 12/2001    0     0     0``````

Not sure how to achieve that? maybe merge or join?

Accepted Solutions
Solution
‎03-13-2017 06:11 PM
Super Contributor
Posts: 268

## Re: Expand rows

Yes, a merge is what you want (or SQL, but then you will have issues on the output dataset's sort order). Note that if there is no match, you'll want to set num_1-num_3 to 0; otherwise they'll be null.

``````data want;
merge dataset_a(in=in_a rename=period=time_period)
dataset_b;
by time_period;
if not in_a then do;
num_1 = 0;
num_2 = 0;
num_3 = 0;
end;
run;``````

All Replies
Solution
‎03-13-2017 06:11 PM
Super Contributor
Posts: 268

## Re: Expand rows

Yes, a merge is what you want (or SQL, but then you will have issues on the output dataset's sort order). Note that if there is no match, you'll want to set num_1-num_3 to 0; otherwise they'll be null.

``````data want;
merge dataset_a(in=in_a rename=period=time_period)
dataset_b;
by time_period;
if not in_a then do;
num_1 = 0;
num_2 = 0;
num_3 = 0;
end;
run;``````
Valued Guide
Posts: 540

## Re: Expand rows

[ Edited ]

A SQL left join would do the trick equally well. It can also be contructed in EG using the query builder.

``````proc sql;
select b.time_period, coalesce(a.num_1, 0), coalesce(a.num_2, 0), coalesce(a.num_3, 0)
from b left join a
on a.time_period=b.time_period;
quit;``````

Add an ORDER BY clause if you need the results sorted.

Hope this helps,

- Jan.

Super Contributor
Posts: 268

## Re: Expand rows

It won't sort, though, because it's a text string…
Valued Guide
Posts: 540

## Re: Expand rows

True, sort of. It will sort but not in a date order. Merely alphabetically and indeed probably not what you want.

- Jan
Super User
Posts: 13,942

## Re: Expand rows

[ Edited ]

For consideration

```data have;
length Time_Period \$ 18.;
Time_Period ="7/2001 - 9/2001";output;
Time_Period ="1/2001 - 3/2001";output;
Time_Period ="5/2002 - 6/2002";output;
Time_Period ="4/2001 - 6/2001";output;
Time_Period ="10/2001 - 12/2001";output;
;
run;

proc sql;
create table want as
select *
from have
order by input(scan(time_period,1,' '),anydtdtm32.)
;
quit;```

Valued Guide
Posts: 540

## Re: Expand rows

A learning experience since I never knew about linguistic sorting. Thanks for that. Only, if you add rows for 2002 (01/2002...) it doesn't quite give the result I would hope for. Oh well, still added something to my bag of tricks :-)
Valued Guide
Posts: 540