incorrect SQL generated to summarize data

Open
#41 0 comments 0 reactions 1 assignee View on GitHub

@2TomLi is already working on this.

Since Apr 8, 2024.

Assessment

This issue has not been assessed yet.

Description

bug

/* summarize sashelp.class with one row per age and a variable called "namelist" containing a comma delimited list of names */
Select Generate Code.

The generated DATA step works if sashelp.class is sorted by AGE:
data summarized_class;
set sashelp.class;
by age;

length namelist $200;
retain namelist;

if first.age then namelist = name;
else namelist = catx(',', namelist, name);

if last.age then output;
drop name;

run;

The generated SQL with does not create the same or desired output. The name do not get concatenated per age.
proc sql;
create table summary as
select age,
catx(", ", name) as namelist
from sashelp.class
group by age;
quit;

Also generates this log message:
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING
clause of the associated table-expression referenced a summary function.

Other incorrect SQL variations generated when I tweaked the comment language:

proc sql;
create table new_dataset as
select age,
catx(',', name_list) as namelist
from (select age,
catx(',', name) as name_list,
count(*) as count
from sashelp.class
group by age, name
having count > 0);
quit;

proc sql;
create table new_dataset as
select age,
catx(',', listagg(name, ',')) as namlist /* do not think listagg() is a SAS function *?
from sashelp.class
group by age;
quit;

Dominant language
TypeScript
Stars
12
Forks
1
Avg merge
4m
Merged PRs (30d)
1

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from sassoftware/sas-copilot

All issues in sassoftware/sas-copilot

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.