BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
LaurieF
Barite | Level 11

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

7 REPLIES 7
LaurieF
Barite | Level 11

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
Rhodochrosite | Level 12

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
Barite | Level 11
It won't sort, though, because it's a text string…
jklaverstijn
Rhodochrosite | Level 12
True, sort of. It will sort but not in a date order. Merely alphabetically and indeed probably not what you want.

- Jan
ballardw
Super User

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
Rhodochrosite | Level 12
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
Rhodochrosite | Level 12
That was in reply to a message that suddenly disappeared.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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