topic Re: LLM to review SAS coding? Which is best? in SAS Programming

LLM to review SAS coding? Which is best?

NKormanik — Wed, 05 Jun 2024 21:48:55 GMT

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

Re: LLM to review SAS coding? Which is best?

ballardw — Wed, 05 Jun 2024 22:10:12 GMT

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.

Re: LLM to review SAS coding? Which is best?

NKormanik — Wed, 05 Jun 2024 23:46:47 GMT

"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.

Re: LLM to review SAS coding? Which is best?

SASKiwi — Thu, 06 Jun 2024 00:02:04 GMT

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.

Re: LLM to review SAS coding? Which is best?

PaigeMiller — Thu, 06 Jun 2024 11:51:09 GMT

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.

Re: LLM to review SAS coding? Which is best?

NKormanik — Sat, 08 Jun 2024 05:32:16 GMT

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

The art of using macros is a bit tricky?

Re: LLM to review SAS coding? Which is best?

PaigeMiller — Sat, 08 Jun 2024 08:56:11 GMT

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.

Re: LLM to review SAS coding? Which is best?

Quentin — Sat, 08 Jun 2024 13:48:27 GMT

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.

Re: LLM to review SAS coding? Which is best?

Quentin — Sat, 08 Jun 2024 14:00:24 GMT

@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.

Re: LLM to review SAS coding? Which is best?

NKormanik — Sat, 08 Jun 2024 22:07:33 GMT

"...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..."

Re: LLM to review SAS coding? Which is best?

Quentin — Sat, 08 Jun 2024 23:01:17 GMT

@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.

Re: LLM to review SAS coding? Which is best?

NKormanik — Tue, 11 Jun 2024 06:11:01 GMT

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;

%mend;

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

%analyze_duads_batches;

Re: LLM to review SAS coding? Which is best?

PaigeMiller — Tue, 11 Jun 2024 10:46:49 GMT

@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.

Re: LLM to review SAS coding? Which is best?

ballardw — Tue, 11 Jun 2024 13:51:08 GMT

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"?

Re: LLM to review SAS coding? Which is best?

PaigeMiller — Tue, 11 Jun 2024 15:27:19 GMT

@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!)

Re: LLM to review SAS coding? Which is best?

illmatic — Tue, 30 Jul 2024 17:09:36 GMT

I have had excellent success with ChatGPT 4o when comparing it to Claude and Gemini. It is by far the best for SAS code creation, debugging, etc.

I have also tried the plug-in for VS Code for SAS so that I may use GitHub's CoPilot, and it's also pretty good, but there were some features that were missing in the plug-in that I needed for my tasks, so I have to abandon it for now.

For your particular scenario, ChatGPT 4o can debug and work it's way out of logic issues, but the only annoying things right now for me is feeding ChatGPT background information (i.e. characteristics of a dataset, in which I will copy-paste the proc contents into chatgpt) and the lack of an AutoGPT so it can debug and test the code itself without me having to go back-and-forth. If ChatGPT could get better insight into the data structures referenced in my code + AutoGPT, I'd be in heaven.

Re: LLM to review SAS coding? Which is best?

Reeza — Tue, 30 Jul 2024 17:48:07 GMT

What is the prompt used?

Re: LLM to review SAS coding? Which is best?

illmatic — Tue, 30 Jul 2024 17:56:31 GMT

"I am attempting to... my source dataset is called .... here is the proc contents of said dataset: "pasted proc contents". And then of course it's being as detailed as possible in my explanation and reminding ChatGPT to ask me clarifying questions instead of assuming. Errors of course are copied from the log and pasted back for debugging, additional context is added when I notice that ChatGPT is falling into a logic trap, but that's much rarer now with 4o.

Re: LLM to review SAS coding? Which is best?

Reeza — Tue, 30 Jul 2024 18:09:34 GMT

Nice, prompts definitely have a big impact on the results in the LLM results. Vague questions get vague responses is very true in LLM. If you can't articulate your problem well, LLMs won't be super effective.

I'm looking at some code specific models (Bamba2) in the near future. Curious to see how those perform.

Re: LLM to review SAS coding? Which is best?

NKormanik — Wed, 31 Jul 2024 02:20:28 GMT

Similar to talking with a human: If you use grade school talk, you don't get far. If you use post grad talk, you get lots further.

ChatGPT and Claude are able to carry on quite well at the post grad level. Not true of most humans.