BookmarkSubscribeRSS Feed
LineMoon
Lapis Lazuli | Level 10

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  
8 REPLIES 8
Reeza
Super User

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 

 

 

LineMoon
Lapis Lazuli | Level 10

@Reeza Thank you for answer.

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

 

Patrick
Opal | Level 21

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?

 

LineMoon
Lapis Lazuli | Level 10

@Patrick Thank you Patrick, that's nice.

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

Patrick
Opal | Level 21

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

LineMoon
Lapis Lazuli | Level 10

   @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  
Patrick
Opal | Level 21

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?

LineMoon
Lapis Lazuli | Level 10

Sorry, I do not understand well your question ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1137 views
  • 1 like
  • 3 in conversation