topic Re: Expand rows in SAS Enterprise Guide

Expand rows

ayin — Wed, 08 Mar 2017 01:21:51 GMT

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?

Re: Expand rows

LaurieF — Wed, 08 Mar 2017 01:49:10 GMT

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;

Re: Expand rows

jklaverstijn — Wed, 08 Mar 2017 15:54:44 GMT

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.

Re: Expand rows

LaurieF — Wed, 08 Mar 2017 16:07:19 GMT

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

Re: Expand rows

jklaverstijn — Wed, 08 Mar 2017 16:10:26 GMT

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

- Jan

Re: Expand rows

ballardw — Wed, 08 Mar 2017 23:37:43 GMT

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;

Re: Expand rows

jklaverstijn — Wed, 08 Mar 2017 23:45:49 GMT

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 🙂

Re: Expand rows

jklaverstijn — Wed, 08 Mar 2017 23:49:19 GMT

That was in reply to a message that suddenly disappeared.