Desktop productivity for business analysts and programmers

Expand rows

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

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: 252

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;

View solution in original post


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

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;
Super Contributor
Posts: 414

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: 252

Re: Expand rows

It won't sort, though, because it's a text string…
Super Contributor
Posts: 414

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: 11,110

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;

 

 

Super Contributor
Posts: 414

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 :-)
Super Contributor
Posts: 414

Re: Expand rows

That was in reply to a message that suddenly disappeared.
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 317 views
  • 4 likes
  • 4 in conversation