Hello experts,
Please, I want to know from your experience( coding, performance) about the automating of sas program fom excel.
I want to use sas code in excel
Thank you
table | vaiable | sas_code_rule |
T1 | tv1 | if tv2=2 then tv1=0 |
T1 | tv2 | when….case... do |
T1 | tv3 | |
T1 | tv4 | |
Tn | tv1 | if tv2=2 then tv1=0 |
Tn | tv2 | when….case... do |
Tn | tv3 | |
Tn | tv4 | |
Tn | tv5 |
I use Excel to feed my code, but not store code such as if/then. If you're capable of IF/THEN in Excel, you should be able to in code as well.
I build my rules based on values I found in a table, otherwise if you need complex logic that can't be handled in Excel, hard code it and DOCUMENT it
@Reeza Thank you for answer.
But I think , we can use mask functions to use code-importing from excel ?
In my experience using Excel as the source for "code injection" and parameter driven generation of dynamic code, is not a good approach. Having said that, I had lately to implement something similar for a very special use case.
I would go for a 2 step approach.
1. Load your data from Excel into a permanent SAS table. This process should also do some syntax validation. You could for example generate a data _null_ step and execute all your code in there. Only load the new data into the permanent parameter table if this data _null_ step executes without any errors.
2. Generate your dynamic code using the permanent SAS table. Below a code sample which might get you started.
/* excel data loaded into SAS work table */
data excel_source;
infile datalines4 truncover dlm=',' dsd ;
input table:$41. variable:$32. code:$200.;
datalines4;
sashelp.class,age,if age<15 then age=10; else age=20;
sashelp.othertable,age,if age=99
sashelp.class,sex,if sex='M' then sex='m';
;;;;
run;
/* syntax check source data */
filename codechk temp;
data _null_;
file codechk;
set excel_source end=last;
if _n_=1 then put 'data _null_;';
put @3 code;
if last then put 'run;';
run;
%include codechk /source2;
/* only load parameter table if there is no error */
data param;
set excel_source;
run;
/* sample for dynamic code */
filename codegen temp;
data _null_;
file codegen;
set param(where=(upcase(table)=upcase("SASHELP.CLASS"))) end=last;
if _n_=1 then
do;
put
'data want;'
/ ' set ' table ';'
;
end;
put @3 code;
if last then
do;
put 'run;';
end;
run;
%include codegen /source2;
...but again: I'd consider such an approach only as the right thing to do in very rare and "exotic" circumstances. What's your use case for this?
@Patrick Thank you Patrick, that's nice.
So, what's about the sas mask functions and sas rules in excel ?
@LineMoon Not sure that I understand your question? Can you please elaborate a bit more?
@Patrick Sorry for that,
Ok, I will do
if the excel column "sas_code_rule" contains a sas code with : if, then, do, while, for , when, set, contain, like, end, or .....
after the importing, I need to mask the code during the compilation by the mask function %quote, %nrquote,... ?
table | vaiable | sas_code_rule |
T1 | tv1 | if tv2=2 then tv1=0 |
T1 | tv2 | when….case... do |
T1 | tv3 | |
T1 | tv4 | |
Tn | tv1 | if tv2=2 then tv1=0 |
Tn | tv2 | when….case... do |
Tn | tv3 | |
Tn | tv4 | |
Tn | tv5 |
You don't need any masking with the code sample I've posted. Everything is treated as text only. Compilation of the data step only happens as part of the %include statement - and there you want the SAS statements to get compiled.
How are you generating the code so that you need macro level masking?
Sorry, I do not understand well your question ?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.