Help with Macro data creation for SQL

[hhchenfx]

Hi Everyone,

I have a macro as follow:

%macro myproblem (X=);

...

...

data final_&X; set ....;

%mend;

% myproblem(X=1);

% myproblem(X=2);

% myproblem(X=3);

of course after running that macro, I have final_1, final_2, final_3.

I want to create a data file call FINAL that is the combination of the above 3 files based on SQL like:

proc sql;

create table FINAL as select * from final_1 full join final_2 on final_1.name=final_2.name; quit;

proc sql;

create table FINAL as select * from FINAL full join final_3 on final_1.name=final_2.name; quit;

I wonder how can I "include" that sql section in the MAcro so it will do the combination anytime new file final_i is created.

The critical point is that I have nothing to start with at the first iteration (X=1) to do the SQL.

Any help is very much appreciated.

Have a nice weekend.

HHC

-----------------

%if ( &RR=5 and &sl_code=0 ) %then %do;
data single_factor_Buy_f; set single_factor_Buy;run;
%end;
%else %do;
proc sql;
create table single_factor_Buy_f
as select * from single_factor_Buy_f as a left join single_factor_Buy as b
on a.a_name = b.a_name and a.a_value = b.a_value; quit;
%end;

[Tom · Accepted solution]

%macro myproblem (X=);

...

...

data final_&X; set ....;

proc sql ;

%if &x=1 %then %do;

  create table final as select * from final_&x ;

%end;

%else %do;

  create table final as

     select *

     from final a full join final_&x b

     on a.name = b.name

  ;

%end ;

quit ;

%mend myproblem;

Or to make it more general you might trigger on FINAL not existing instead of the value of &X.

%if not %sysfunc(exist(final)) %then %do;

  data final;

    set final_&x (obs=0 keep=name);

  run;

%end;

proc sql ;

  create table final as

     select *

     from final a full join final_&x b

     on a.name = b.name

  ;

quit;

[hhchenfx]

Thank you so much, Tom.

I like the first method since I also tried it but failed since I don't know how to use the %if.

HHC

[hhchenfx]

HI Tom,

If X= character instead of number, should I make any change since the following is not working as the column name all turn in to missing value.

%if &x='abc' %then %do;

Thank you,

HHC

[Tom]

Not sure what you mean about missing values as there are no variables referenced in the code snippets.

To macro code everything is character so do NOT include the quotes unless they are actually part of the string.

So if you want to test if the value of macro variable X is the character string abc you would write %IF (&X=abc) %THEN .....

Also I am not sure if the join step you are doing in SQL is what you want to do, but would need to know more about the data to advise.  If you just want to append the new records to the bottom of the file then use PROC APPEND.  If you want to merge on new columns (append on the right) then I would recommend a data step MERGE instead of an SQL join.

[hhchenfx]

Thanks, Tom.

You are right. I should use merge in this case since it is only 1-to-1 matching.

Also the (%IF=abc) works perfectly.

HHC