Expand rows

[ayin]

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?

[LaurieF · Accepted solution]

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;

[jklaverstijn]

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.

[LaurieF]

It won't sort, though, because it's a text string…

[jklaverstijn]

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

- Jan

[ballardw]

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;

[jklaverstijn]

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 🙂

[jklaverstijn]

That was in reply to a message that suddenly disappeared.