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
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!
sorry the code is
data want;
merge have 1 (in=a)
have 2 (in=b );
by date_id;
if b;
run;
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.
My fault. I added ORDER BY as an afterthought. The semicolon on the previous line should be removed.
Rather than posting the program, you will need to post the log. That's what explains the nature of the error.
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!
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: