topic Help with Macro data creation for SQL in SAS Programming

Help with Macro data creation for SQL

hhchenfx — Fri, 27 Jan 2017 16:18:30 GMT

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;

Re: Help with Macro data creation for SQL

Tom — Sat, 05 Apr 2014 03:27:20 GMT

%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;

Re: Help with Macro data creation for SQL

hhchenfx — Sat, 05 Apr 2014 15:02:56 GMT

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

Re: Help with Macro data creation for SQL

hhchenfx — Sat, 05 Apr 2014 15:24:40 GMT

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

Re: Help with Macro data creation for SQL

Tom — Sat, 05 Apr 2014 15:55:08 GMT

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.

Re: Help with Macro data creation for SQL

hhchenfx — Sat, 05 Apr 2014 16:59:33 GMT

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