There are two ways to implement macro statements, in fact, there is no syntax problem in both methods, but why the first one works and the second does not complete.
/*there is macro code*/
%MACRO IMPORT(FILE,OUTFILE,SHEETFILE,DATANAME);
%LET FILE=%SYSFUNC(DEQUOTE(&FILE));
%LET OUTFILE=%SYSFUNC(DEQUOTE(&OUTFILE));
%LET SHEETFILE=%SYSFUNC(DEQUOTE(&SHEETFILE));
%LET DATANAME=%SYSFUNC(DEQUOTE(&DATANAME));
PROC IMPORT
DATAFILE="&FILE"
OUT=&OUTFILE DBMS=EXCEL REPLACE;
SHEET=&SHEETFILE;
GETNAMES=YES;
RUN;
PROC DS2;
DATA &DATANAME (OVERWRITE=YES);
DCL VARCHAR CITY HAVING LABEL '城市';
DCL DOUBLE _COL1 HAVING LABEL '人均GDP(元)';
DCL DOUBLE _COL2 HAVING LABEL '第二、三产业比重';
DCL DOUBLE _COL3 HAVING LABEL '城乡居民人均可支配收入(元)';
DCL DOUBLE _COL4 HAVING LABEL '税收收入';
DCL DOUBLE _COL5 HAVING LABEL '社会消费品零售总额(亿元)';
DCL DOUBLE _COL6 HAVING LABEL '城镇非私营单位人员年平均工资(元)';
DCL DOUBLE _COL7 HAVING LABEL '固定资产投资额(亿元)';
DCL DOUBLE _COL8 HAVING LABEL '电信业务总量(亿元)';
DCL DOUBLE _COL9 HAVING LABEL '旅游总消费(亿元)';
DCL DOUBLE _COL10 HAVING LABEL '工业总产值(亿元)';
DCL DOUBLE _COL11 HAVING LABEL '第二产业增加值(亿元)';
DCL DOUBLE _COL12 HAVING LABEL '第三产业增加值(亿元)';
METHOD RUN();
SET &OUTFILE ;
END;
ENDDATA;
RUN;
QUIT;
%MEND IMPORT;
/*This is the first way macro code is implemented*/
%IMPORT(C:\Users\Administrator\OneDrive\aaa.xlsx
,_&A,_&A,PCA_&A);
%IMPORT(C:\Users\Administrator\OneDrive\aaa.xlsx
,_&B,_&B,PCA_&B);
%IMPORT(C:\Users\Administrator\OneDrive\aaa.xlsx
,_&C,_&C,PCA_&C);
%IMPORT(C:\Users\Administrator\OneDrive\aaa.xlsx
,_&D,_&D,PCA_&D);
/*This is the second way macro code is implemented.But I want to implement macro statements differently*/
PROC FCMP OUTLIB=WORK.FUNCSS.MATH;
FUNCTION IMPORT_MACRO(OUTFILE $,SHEETFILE $,DATANAME $);
DATA=TRIM(LEFT('PCA'||DATANAME));
FILE='C:\Users\Administrator\OneDrive\aaa.xlsx';
RC=RUN_MACRO('IMPORT',FILE,OUTFILE,SHEETFILE,DATA);
RETURN(RC);
ENDSUB;
RUN;
QUIT;
OPTIONS CMPLIB=(WORK.FUNCSS);
DATA _NULL_;
DO I="_&A","_&B","_&C","_&D";
RC=IMPORT_MACRO(I,I,I);
END;
RUN;
Can anyone tell me what's wrong with the second way? How to modify?
I got your code to work
%Macro sorts;
%let v= %sysfunc(dequote(&v));
%let d= %sysfunc(dequote(&d));
proc sort Data =&d out=srtd;
by &v;
Run;
proc print Data =srtd;
by &v;
Run;
%mend;
proc fcmp outlib=work.funcs.math;
function yyy(d $,v $);
rc=Run_Macro('sorts',d,v);
return(rc);
endsub;
run;
Options cmplib=work.funcs;
Data _Null_;
rc=yyy('sashelp.class','sex');
Run;
But why all this complication?
Here is a simpler approach, at least for this example
%let d= sashelp.class;
%let v= sex;
Data _Null_;
rc=dosubl("proc sort data=&d out=srtd ; by &v; run; Proc print data=srtd; by &v; run;");
Run;
The dosubl function is very versatile.
Just a thought
I just want to achieve the same goal in different ways, like the following example
OPTIONS CMPLIB=(WORK.FUNCS);
DATA SIGMA;
INFILE DATALINES DLM='';
INPUT SIGMA;
RETAIN MEAN_SIGMAS MEAN 0;
MEAN=MEAN+SIGMA;
MEAN_SIGMAS=MEAN_SIGMA(MEAN,_N_);/*累加并调用FCMP函数二*/
PUT MEAN_SIGMAS;
DATALINES;
0.8215555
1.0417353
1.3868175
1.4096968
1.2387825
1.2348056
1.0817474
1.3015245
1.4188027
1.7929420
1.9840148
1.2696905
1.8229448
;
PROC DS2;
DATA _NULL_ ;
DROP I;
DCL PACKAGE FUNCTION_PACK P();
DCL DOUBLE DS2_ARRAY[1,13];
DCL DOUBLE DS2_VAR MEAN_SIGMAS MEAN I;
RETAIN MEAN_SIGMAS MEAN;
METHOD RUN();
DS2_ARRAY:=(
0.8215555,
1.0417353,
1.3868175,
1.4096968,
1.2387825,
1.2348056,
1.0817474,
1.3015245,
1.4188027,
1.7929420,
1.9840148,
1.2696905,
1.8229448
);
DO I =1 TO DIM(DS2_ARRAY,2);
DS2_VAR=DS2_ARRAY[DIM(DS2_ARRAY),I];
MEAN+DS2_VAR;
MEAN_SIGMAS=P.MEAN_SIGMA(MEAN,DIM(DS2_ARRAY,2));
OUTPUT;
END;
PUT MEAN_SIGMAS= _N_=;
END;
RUN;
The above two different codes actually achieve the same purpose.
Try using Single Quote (') instead of Double quote (")
DATA _NULL_;
DO I="_&A","_&B","_&C","_&D";
RC=IMPORT_MACRO(I,I,I);
END;
RUN;
I suspect the SAS compiler assumes &A, &B, &C, &D are macros and tries to resolve them because they are included in " "
%LET A=2021;
%LET B=2020;
%LET C=2019;
%LET D=2018;
PROC FCMP OUTLIB=WORK.FUNCSS.MATH;
FUNCTION IMPORT_MACRO(OUTFILE $,SHEETFILE $,DATANAME $);
DATA=('PCA'||TRIM(LEFT(DATANAME)));
FILE=('"C:\Users\Administrator\OneDrive\桌面\新建文件夹\第二步:基于主成分分析的广西经济发展水平得分排名\主成分分析数据.xlsx"');
RC=RUN_MACRO('IMPORT',FILE,OUTFILE,SHEETFILE,DATA);
RETURN(RC);
ENDSUB;
RUN;
QUIT;
OPTIONS CMPLIB=(WORK.FUNCSS);
DATA _NULL_;
DO I='"_&A"','"_&B"','"_&C"','"_&D"';
RC=IMPORT_MACRO(I,I,I);
END;
RUN;
I tried to change the method many times, but all failed, it seems that this method cannot add macro variables.
According to the online documentation SAS Help Center: Syntax: PROC FCMP PROC FCMP Statement
Note: The DATA option can send inputs to a function or subroutine that are defined in the PROC FCMP step. The PROC FCMP step iterates through each observation and calls the function or subroutine during each iteration.
Is that what you are trying to do with your function?
I think you need to re-check how you are defining your function first, then try to work out the issue with your parameter passing.
Did you test your function by calling it directly with manually supplied parameters?
I got your code to work
%Macro sorts;
%let v= %sysfunc(dequote(&v));
%let d= %sysfunc(dequote(&d));
proc sort Data =&d out=srtd;
by &v;
Run;
proc print Data =srtd;
by &v;
Run;
%mend;
proc fcmp outlib=work.funcs.math;
function yyy(d $,v $);
rc=Run_Macro('sorts',d,v);
return(rc);
endsub;
run;
Options cmplib=work.funcs;
Data _Null_;
rc=yyy('sashelp.class','sex');
Run;
But why all this complication?
Here is a simpler approach, at least for this example
%let d= sashelp.class;
%let v= sex;
Data _Null_;
rc=dosubl("proc sort data=&d out=srtd ; by &v; run; Proc print data=srtd; by &v; run;");
Run;
The dosubl function is very versatile.
Just a thought
Try this instead. DATA= is a reserved word!
PROC FCMP OUTLIB=WORK.FUNCSS.MATH;
FUNCTION IMPORT_MACRO(OUTFILE $,SHEETFILE $,DATANAME $);
DsName='PCA'||STRIP(DATANAME);
FName='"C:\Users\Administrator\OneDrive\桌面\新建文件夹\第二步:基于主成分分析的广西经济发展水平得分排名\主成分分析数据.xlsx"';
RC=RUN_MACRO('IMPORT',FName,OUTFILE,SHEETFILE,DsName);
RETURN(RC);
ENDSUB;
RUN;
Once I changed DATA= to DsName= the Syntax Linter in SAS Enterprise Guide turned Blue rather than Red.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.