topic Re: automate sas program by using excel in SAS Programming

automate sas program by using excel

LineMoon — Sat, 26 Nov 2016 23:40:24 GMT

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

Re: automate sas program by using excel

Reeza — Sun, 27 Nov 2016 00:42:06 GMT

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

Re: automate sas program by using excel

Patrick — Sun, 27 Nov 2016 00:56:13 GMT

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?

Re: automate sas program by using excel

LineMoon — Sun, 27 Nov 2016 10:15:18 GMT

@Reeza Thank you for answer.

But I think , we can use mask functions to use code-importing from excel ?

Re: automate sas program by using excel

LineMoon — Sun, 27 Nov 2016 10:17:42 GMT

@Patrick Thank you Patrick, that's nice.

So, what's about the sas mask functions and sas rules in excel ?

Re: automate sas program by using excel

Patrick — Sun, 27 Nov 2016 10:20:28 GMT

@LineMoon Not sure that I understand your question? Can you please elaborate a bit more?

Re: automate sas program by using excel

LineMoon — Sun, 27 Nov 2016 10:55:44 GMT

@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

Re: automate sas program by using excel

Patrick — Sun, 27 Nov 2016 11:20:34 GMT

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?

Re: automate sas program by using excel

LineMoon — Sun, 27 Nov 2016 11:30:57 GMT

Sorry, I do not understand well your question ?