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

Hello. I have two datasets- what I would like to do is that combine two dataset by group and month_year but also include the ones that do not overlap with dates:

Dataset A

month_yearexpected_rate
1/1/20170
2/1/20175
3/1/201710
4/1/201715
5/1/201735
6/1/201745
7/1/201751
8/1/201753
9/1/201759
10/1/201765
11/1/201795
12/1/2017100

Dataset B

groupactual_ratermd_my
A22/1/2017
A73/1/2017
A5110/1/2017
A6711/1/2017
B01/1/2017
B42/1/2017
B104/1/2017
B255/1/2017
B6512/1/2017

 

WANT:

groupmonth_yearexpected_rateactual_rate
A1/1/20170 
A2/1/201752
A3/1/2017107
A4/1/201715 
A5/1/201735 
A6/1/201745 
A7/1/201751 
A8/1/201753 
A9/1/201759 
A10/1/20176551
A11/1/20179567
A12/1/2017100 
B1/1/201700
B2/1/201754
B3/1/201710 
B4/1/20171510
B5/1/20173525
B6/1/201745 
B7/1/201751 
B8/1/201753 
B9/1/201759 
B10/1/201765 
B11/1/201795 
B12/1/201710065

 

I tried the following proc sql command:

Proc sql;


SELECT * FROM dataset_a
FULL JOIN dataset_b 
ON dataset_b.month_year= dataset_a.month_year

I also tried full outer join, right join. None of them seem to work

1 ACCEPTED SOLUTION

Accepted Solutions
MarkDawson
SAS Employee
In case I'd introduced a syntax error when replying, I tested the code above and didn't see any errors.
What version of SAS are you using - use %put &=SYSVLONG4 &=SYSSCPL ;
Please can you add the log showing the whole step and error

View solution in original post

3 REPLIES 3
MarkDawson
SAS Employee

I believe this will give you what you want...

data sample_a ;
   input month_year:ddmmyy10. expected_rate ;
   format month_year ddmmyy10. ;
datalines ;
1/1/2017	0
2/1/2017	5
3/1/2017	10
4/1/2017	15
5/1/2017	35
6/1/2017	45
7/1/2017	51
8/1/2017	53
9/1/2017	59
10/1/2017	65
11/1/2017	95
12/1/2017	100
; run ; 
data sample_b ;
   input group $ actual_rate rmd_my:ddmmyy8. ;
   format rmd_my ddmmyy10. ;
datalines ;
A	2	2/1/2017
A	7	3/1/2017
A	51	10/1/2017
A	67	11/1/2017
B	0	1/1/2017
B	4	2/1/2017
B	10	4/1/2017
B	25	5/1/2017
B	65	12/1/2017
; run ;  

proc sql ; 
   create table results as 
      select 'A' as group, sample_a.month_year, sample_a.expected_rate, sample_b.actual_rate 
        from sample_a
        left join sample_b 
             on sample_a.month_year = sample_b.rmd_my and sample_b.group = 'A' /* Gets all the data for Group A */
      outer union corresponding  /* This is a concatenation of the results set before and after */
      select 'B' as group, sample_a.month_year, sample_a.expected_rate, sample_b.actual_rate 
        from sample_a
        left join sample_b 
             on sample_a.month_year = sample_b.rmd_my and sample_b.group = 'B' /* Gets all the data for Group B */ ; 
quit ; 

but if you need something a little more dynamic that does not need you to know the values of Group, try this - its harder to follow but looks like the results are good

proc sql  ; 
   create table results as 
      select c.group, c.month_year, c.expected_rate, d.actual_rate
        from (select a.month_year, a.expected_rate, b.group 
                 from sample_a a, 
                 (select distinct b.group from sample_b b) ) c /* this gives you all the combinations of month_year and group, along with expected_rate */ 
        left join sample_b d
             on c.month_year = d.rmd_my and c.group=d.group /* and this gives you the actual rates */
      order by c.group, c.month_year ;
quit ; 
radhikaa4
Calcite | Level 5

Thank you! Just a quick question - I tried the second block of the code, but I am getting error at " b) ) c" . it says syntax error but I tried to remove ) and add it else where but still doesn't work

MarkDawson
SAS Employee
In case I'd introduced a syntax error when replying, I tested the code above and didn't see any errors.
What version of SAS are you using - use %put &=SYSVLONG4 &=SYSSCPL ;
Please can you add the log showing the whole step and error

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 735 views
  • 0 likes
  • 2 in conversation