DATA Step, Macro, Functions and more

Merging transaction dataset with a calendar dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

Merging transaction dataset with a calendar dataset

Hi All,

 

I have the 2 datasets attached

 

- First table contains the transactions dates of customer A and customer B

- Second table containts the calendar dates and week_start_date and week_end_date ( from 01/01/2017 to 01/04/2017)

 

I want to merge the datasets and to produce one table contains both customers dates and calendar weeks but I want the same number rows than the calendar for each customer. For example if on 01/01/2017, they haven' transacted , I want to see a missing.

 

When I use the code below, it works for the first customer but for the second, it only displays the dates when he transacted.

 

data want;

  merge  have 1  (in=a)

              have 2  (in=b );

   by date_id;

   if a;

run;

 

Your help would be much appreciated

 

Thank You


Accepted Solutions
Solution
‎04-20-2017 10:47 AM
Super User
Posts: 5,082

Re: Merging transaction dataset with a calendar dataset

OK.  I'm sure there are SQL guys out there that can do this in one step.  But my SQL isn't the strongest, so here's a two-step version:

 

proc sql;

create table owner_list as

select distinct owner from have1;

create table want2 as

select * from have2, owner_list

order by owner date_id;

quit;

 

Hoping that's the last change!

View solution in original post


All Replies
Frequent Contributor
Posts: 96

Re: Merging transaction dataset with a calendar dataset

sorry the code is 

 

data want;

  merge  have 1  (in=a)

              have 2  (in=b );

   by date_id;

   if b;

run;

Super User
Posts: 5,082

Re: Merging transaction dataset with a calendar dataset

The result you are for is impractical.  There are multiple records that are identical, with no way (except their current order) to tell which customer they belong to. 

 

As an approach, you will need to add another variable that indicates the "owning" customer.  For example:

 

data want1;

set have1;

owner = customer_id;

run;

 

proc sql;

create table want2 as

select distinct owner from have1,

* from have2;

order by owner date_id;

quit;

 

data want;

merge want1 want2;

by owner date_id;

run;

 

Keep the extra variable OWNER in there.  You don't have to print it, but you will need it for any sort of analysis.

 

 

Frequent Contributor
Posts: 96

Re: Merging transaction dataset with a calendar dataset

Hi Astounding,

Thank you for your email..
I get an error when I run the code below. Is the syntax correct?

Thank you

*proc sql;*

*create table want2 as*

*select distinct owner from have1,*

** from have2;*

*order by owner date_id;*

*quit;*
*##- Please *type your reply above this line. Simple formatting, no
attachments. -##
Super User
Posts: 5,082

Re: Merging transaction dataset with a calendar dataset

My fault.  I added ORDER BY as an afterthought.  The semicolon on the previous line should be removed.

Frequent Contributor
Posts: 96

Re: Merging transaction dataset with a calendar dataset

Hi Astounding,

I have removed the semicolon, but still get error. The error comes from
this highlighetd in red (*from have2)

Thank you


proc sql;

create table want2 as

select distinct owner from have1,

* from have2

order by owner date_id;

quit;


##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Posts: 5,082

Re: Merging transaction dataset with a calendar dataset

Rather than posting the program, you will need to post the log.  That's what explains the nature of the error.

Frequent Contributor
Posts: 96

Re: Merging transaction dataset with a calendar dataset

Please find below the log. Thank you

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 proc sql;
25
26 create table work.want2 as
27
28 select distinct owner from work.OC_12w_trans,
29
30 * from work.events_all_12w
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: a name, a
quoted string, (, CONNECTION, DICTIONARY.

ERROR 76-322: Syntax error, statement will be ignored.

31
32 order by owner date_id;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
33
34 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Solution
‎04-20-2017 10:47 AM
Super User
Posts: 5,082

Re: Merging transaction dataset with a calendar dataset

OK.  I'm sure there are SQL guys out there that can do this in one step.  But my SQL isn't the strongest, so here's a two-step version:

 

proc sql;

create table owner_list as

select distinct owner from have1;

create table want2 as

select * from have2, owner_list

order by owner date_id;

quit;

 

Hoping that's the last change!

Frequent Contributor
Posts: 96

Re: Merging transaction dataset with a calendar dataset

Thank You for your help Astounding! It now works 😊



##- Please type your reply above this line. Simple formatting, no
attachments. -##
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 124 views
  • 0 likes
  • 2 in conversation