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 ?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.