DATA Step, Macro, Functions and more

automate sas program by using excel

Reply
Super Contributor
Posts: 371

automate sas program by using excel

[ Edited ]

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  
Super User
Posts: 17,771

Re: automate sas program by using excel

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 

 

 

Super Contributor
Posts: 371

Re: automate sas program by using excel

@Reeza Thank you for answer.

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

 

Respected Advisor
Posts: 3,887

Re: automate sas program by using 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?

 

Super Contributor
Posts: 371

Re: automate sas program by using excel

@Patrick Thank you Patrick, that's nice.

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

Respected Advisor
Posts: 3,887

Re: automate sas program by using excel

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

Super Contributor
Posts: 371

Re: automate sas program by using excel

[ Edited ]

   @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  
Respected Advisor
Posts: 3,887

Re: automate sas program by using excel

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?

Super Contributor
Posts: 371

Re: automate sas program by using excel

Sorry, I do not understand well your question ?

Ask a Question
Discussion stats
  • 8 replies
  • 265 views
  • 1 like
  • 3 in conversation