Hello!
I've beeing build several databases in a macro as follows:
FUTURE_ORD_201601
FUTURE_ORD_201602
FUTURE_ORD_201603
.
.
.
FUTURE_ORD_201903
I have created a table (ACCOUNT_NUMBER) that contains all the account numbers that are in common (it will be the key to be merged)
ACCOUNT_NUMBER
1
2
3
.
.
.
1894039183298219219
I'm mergin all the datasets as follows
DATA MERGE;
MERGE ACCOUNT_NUMBER(IN=A);
FUTURE_ORD_: (IN=B);
BY ACCOUNT_NUMBER;
IF A;
RUN;
I wonder if I can include someway the FUTURE databases into the macre where is created in order to merge the FUTURE database in every single step and let fix the ACCOUNT_NUMBER database as pivot for the merging.
This is what I've beeing doing so far:
proc sql; create table future as account_number from DATABASE; RUN; %MACRO FUTURE(VAR1,VAR2,VAR3); creation of the database; order of every single database created into the macro with the name FUTURE_ORD_&DATE. %MEND; DATA MERGE; MERGE future (IN=A); FUTURE_ORD_;(IN=C); by account_number; if A; run;
I want to make the merge inside the macro and be sure that in every single step the merge is beeing doing in propper order.
Thanks in regards!!
I would really like to see the log of submitting this code if it is actually accomplishing what you want.
DATA MERGE; MERGE future (IN=A); FUTURE_ORD_;(IN=C); by account_number; if A; run;
I suspect that you have two too many semicolons in that code.
The merge statement you've posted can't work like @ballardw is hinting. Working code needs a semicolon removed and could look like:
data merge;
merge account_number(in=a) future_ord_: ;
by account_number;
if a;
run;
I believe though you don't really want to merge the data but you want to stack all the rows with a matching account number to your reference table with account numbers. If so then code as below should do the job.
data stacked;
if _n_=1 then
do;
dcl hash h1(dataset:'account_number(keep=account_number)');
h1.definekey('account_number');
h1.definedata('account_number');
h1.definedone();
end;
set future_ord_: ;
if h1.check()=0;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.