LLM to review SAS coding? Which is best?

[NKormanik]

Rather than asking on this forum to "please check my SAS code and tell me what's wrong?", I decided to ask Anthropic's Claude for help.

Well, way too often what Claude suggested simply did not work.  I asked again, and again.  Claude kept trying to change the SAS code to get it to successfully execute.  But, alas, it turned out to be mostly a waste of time.

So, just curious what your take is.  Do ANY of the LLMs know SAS fairly well, enough for us to post some code and ask it to show where problems are occurring?

Your thoughts greatly appreciated.

Nicholas Kormanik
 

[ballardw]

From what has been generated, or at least that the questioner admitted using ChatGPT or similar, to generate code my off the cuff answer is "probably not yet".

I think part of the issue can be that SAS Institute had two families of products, SAS and JMP,  that might confuse some of the models. Also SAS has a few other meanings in other contexts and may have more issues. Plus Enterprise Guide and the changes around Viya/ CAS can't be helping.

Just for fun you might want to peruse Proc Template syntax. Ancient stuff related to Greplay, ODS styles, Graphic Template Language, the table/graph output definitions used by procedures and that just the stuff I'm aware of.

[NKormanik]

"To generate code..."

Actually, the question was, to review code.

And, the more specific question is whether ANY of the current LLMs have a better ability to do this, review SAS code.

One can probably show any of them some SAS code, say, with a mistake or two.  So, how do they handle it?  Do any of them respond, for example, "you are missing a semicolon at this point".

Yes, no, maybe?  Things are what they are.

[SASKiwi]

The most reliable way of reviewing  code is simply to run it. If you get errors, they could be syntax, logic or system errors. An LLM can only check for syntax errors but never system errors as those are only apparent from running code. LLMs might deal with some obvious logic problems but will never know the difference between what you intended to code for versus what you actually wrote unless you tell it what your use case was to start with.

[PaigeMiller]

I agree that LLM do not do a good job of providing good SAS code, except in the simplest of cases.

However, I think there's another problem entirely that LLM doesn't handle at all, but happens quite regularly here in this and other forums. The problem is that people ask the wrong questions, or that people take the wrong approach. This is called the XY Problem, it happens very very often, and LLM models won't address that at all. The XY problem is that someone takes the wrong approach, or an inefficient approach, and then gets stuck coding some hard (or not so hard) part of this wrong approach, and then they come here asking how to solve this code problem. Most of the time, we have to convince the person that there is an easier way that they could approach this problem, and most of the time, the people resist because all they want is how to solve their coding problem, and don't want a different approach or don't want to explain the big picture to us. Most of the time with XY Problems, this leads to very difficult conversations.

LLMs will not address this XY Problem. I believe even you, @NKormanik , have had XY Problems here in this forum. No criticism intended here, its just a description of what happens, it seems that almost everyone (including me) does it at some time.

[NKormanik]

The primary problem, so it seems, has to do with macros.

The art of using macros is a bit tricky?

[PaigeMiller]

I would say the learning curve for using the macro language is steeper and longer than learning Base SAS.

I would also say that many (most?) people take the wrong approach (another XY problem) — they try to write a macro from scratch; they should write Base SAS code that works and does what they want for one or two iterations, and only then try to turn that code into a macro. That will be a bazillion times easier than creating a macro from scratch.

[Quentin]

---

@PaigeMiller wrote:

I would say the learning curve for using the macro language is steeper and longer than learning Base SAS.

Definitely true for a person, and I would think maybe even more true for an LLM. 

If my guess that LLM's had a relatively small amount of SAS code to train on is true, then you've got to think they had a miniscule amount of macro language code.

A key part of a person learning the macro language is to realize is that it's a *different* language than the SAS language, and that it's a language with the purpose of generating code in another language.

So LLM's will be faced with the same challenge of generating text in two different languages.

That said, when I recently asked ChatGPT for some advice about identifying clusters within a time series, it did point me toward a few possible existing PROCS, and at one point it generated a macro.  I never tried to compile the macro, but from a quick read it looked like a reasonable attempt.

[NKormanik]

"...only then try to turn that code into a macro. That will be a bazillion times easier than creating a macro from scratch."

Likely the LLMs go the macro route from scratch.

LLM, "Piece of cake, boys..."

[Quentin]

---

@NKormanik wrote:

"...only then try to turn that code into a macro. That will be a bazillion times easier than creating a macro from scratch."

Likely the LLMs go the macro route from scratch.

LLM, "Piece of cake, boys..."

---

I just prompted ChatGPT 3.5 with "You are an expert in SAS programming. Write a utility macro that will act as a function and return the number of records in a dataset."  and it returned a pretty nice macro:

%macro get_record_count(dataset);
    %local dsid nobs result;
    
    %let dsid = %sysfunc(open(&dataset));
    %let nobs = %sysfunc(attrn(&dsid, nobs));
    
    %if &dsid %then %do;
        %let result = &nobs;
        %let dsid = %sysfunc(close(&dsid));
    %end;
    %else %do;
        %put ERROR: Dataset &dataset not found;
        %let result = -1;
    %end;
    
    &result
%mend;

%let count = %get_record_count(sashelp.class);
%put Number of records in dataset: &count;

And it's like magic watching the text appear.  But okay, it's text prediction, so maybe not that hard.  

Then I asked about a WHERE option.  I asked:

Will it work if I submit:  %let count = %get_record_count(sashelp.class(where=(sex='M'))); 

And it correctly answered:

The macro I provided will not work correctly with a WHERE clause as it is currently written. It will return the number of records in the dataset before applying the WHERE condition.

I was surprised by that.  But then it went on to write a macro that it said would work with a WHERE option, but it still doesn't honor the WHERE:

To make it work with a WHERE condition, you need to modify the macro to accept the WHERE condition as an additional argument and use it in the appropriate place. Here's the modified macro:

%macro get_record_count(dataset, where_condition=);
    %local dsid nobs result;

    %if (%length(&where_condition) > 0) %then %do;
        %let dsid = %sysfunc(open(&dataset(where=(&where_condition))));
    %end;
    %else %do;
        %let dsid = %sysfunc(open(&dataset));
    %end;
    
    %let nobs = %sysfunc(attrn(&dsid, nobs));
    
    %if &dsid %then %do;
        %let result = &nobs;
        %let dsid = %sysfunc(close(&dsid));
    %end;
    %else %do;
        %put ERROR: Dataset &dataset not found;
        %let result = -1;
    %end;
    
    &result
%mend;

%let count = %get_record_count(sashelp.class, where_condition=sex='M');
%put Number of records in dataset: &count;

Which is a nice looking macro.  And reading it, it really feels like chatgpt is understanding questions, and the code addresses the question.  Unfortunately the code doesn't work, but it's reasonable to predict that it would work.  The macro will still return 19, because even if you open a dataset with a WHERE option, the attrn(&dsid,nobs) will read the metadata and return the number of records in the dataset ignoring the WHERE condition.  (See  Jack Hamilton's paper https://support.sas.com/resources/papers/proceedings/proceedings/sugi26/p095-26.pdf for a discussion). 

Unfortunately, when I told ChatGPT it didn't work, it kept giving me on variations on the theme which also didn't work.  And I almost gave up.

Then I asked "Could using DOSUBL help" and it dropped:

%macro get_record_count(dataset, where_condition=);
    %local rc result;

    %let rc = %sysfunc(dosubl(%str(
        data _null_;
            if 0 then set &dataset nobs=nobs;
            call symputx('result', nobs);
        run;
    )));

    &result
%mend;

%put Number of records where sex is 'M': %get_record_count(sashelp.class, where_condition=sex='M');

Which is an ok guess at how you could try do it with with DOSUBL.  It's weird that the where_condition parameter is never used, and it wouldn't work if it was, but it's a reasonable try.

So for me, when I play with ChatGPT for SAS, it always feels close to being good, but not yet.  It's fun because I understand SAS enough to know when it's wrong, and why it's wrong . But it's wrong enough about SAS that I'm hesitant to use it for other languages that I don't know as well, because I feel more likely to be led astray.

[Quentin]

At PharmaSUG this year, there were, not surprisingly, a ton of papers about LLM's.   Searching lexjansen.com for LLM will return plenty of hits  https://www.lexjansen.com/search/searchresults.php?q=LLM 

One great presentation assessed several LLM's  with regard to SAS programming.  https://www.lexjansen.com/pharmasug/2024/AP/PharmaSUG-2024-AP-191.pdf 

Interestingly, they found Claude to be the best of what they tested, for both error fixing and code explanation:

I have played just a little with ChatGPT 3.5 and SAS, and haven't been impressed.  But when I've played with asking ChatGPT to generate SQL code or to explain SQL code, I've found it to be helpful.  One thought I have is that there is much more SQL code on the internet (and python code and R code), than there is SAS code.  So the LLM's probably had much more data to train on for those languages.

I'm excited to see what SAS will be able to develop with Viya Copilot.  They were showing it in their booth at PharmaSUG, and also it was on stage at SAS Innovate.  You can write a comment in your IDE (I forget if they showed VScode or Studio) and ask it to generate SAS code, or highlight a block of code and ask it to generate comments, and do similar stuff.  I can't remember which LLM they are using in the background.  But I'm assuming SAS has plenty of SAS code to train their models.  And since they're obviously focused on training a model that will be good for SAS code generation, but doesn't have to be good for generating blog posts or resumes, I expect it will be much better for SAS code generation / explanation than the general purpose LLM's.  Time will tell.

[NKormanik]

Here's one example.  Neither Anthropic's Claude nor ChatGPT can give me a revision that works:

/********** 05.01 Analyze Duads in Batches **********/

%macro analyze_duads_batches;

  %do i = 1 %to &num_batches;

    %let start_row = %eval((&i - 1) * 1000 + 1);

    %let end_row = %eval(&i * 1000);

    proc sql noprint;

      select duad into :duads_batch separated by ' '

      from SAS_1.all_duads

      where row_num between &start_row and &end_row;

    quit;

    %put NOTE: Processing batch &i from row &start_row to &end_row;

    %analyze_duads_batch(&duads_batch);

  %end;

%mend;

/********** 06.01 Analyze Each Duad **********/

%macro analyze_duads_batch(duads_batch);

  %let num_duads = %sysfunc(countw(&duads_batch));

  %do j = 1 %to &num_duads;

    %let duad = %scan(&duads_batch, &j);

    %let var1 = %scan(&duad, 1, '_');

    %let var2 = %scan(&duad, 2, '_');

    %put NOTE: Analyzing duad &duad with variables &var1 and &var2;

    proc reg data=SAS_1.train_data outest=SAS_1.results_&duad (keep=_RSQ_ _P_);

      model &response_var = &var1 &var2;

    run;

    /* Check for errors and log the message */

    %if &syserr ne 0 %then %do;

      %put ERROR: Regression analysis failed for duad &duad.;

    %end;

    %else %do;

      %put NOTE: Successfully processed duad &duad. Results stored in SAS_1.results_&duad.;

    %end;

  %end;

%mend;

/********** 07.01 Execute Duad Analysis **********/

%analyze_duads_batches;

[PaigeMiller]

---

@NKormanik wrote:

Here's one example.  Neither Anthropic's Claude nor ChatGPT can give me a revision that works:

---

You obviously explained a problem to ChatGPT or Claude, and this is the result. Now, explain the problem to us.

[ballardw]

Looks like yet another "split up the data an loop over data sets" instead of the SAS way of adding a BY variable and using BY group analysis with a single data set. Which wouldn't require a macro at all.

And would selection of sequential rows into the model really be best? Or random assignment to "batch"?

[PaigeMiller]

---

@ballardw wrote:

Looks like yet another "split up the data an loop over data sets" instead of the SAS way of adding a BY variable and using BY group analysis with a single data set. Which wouldn't require a macro at all.

---

Good point, I didn't even try to figure out what the code was doing. However, this is the exact sort of XY problem that LLMs will not catch, and will lead the user down inefficient paths (if the user can get it to work!)