OHDSI Home | Forums | Wiki | Github

ATLAS Custom Feature Analysis: group by care_site_id

Developers
#1

Hi,

I have been trying to create a cohort characterization in ATLAS where visit counts are grouped by care_site_id. I have been thinking that a custom feature analysis is the best way to do this, but I have been unsuccessful (getting sql errors). Has anyone done this in ATLAS or can help advise on how to do this? Here is the custom feature analysis I created that results in the error “bad SQL grammar.”

– Custom analysis producing visit counts grouped by care_site_id
select cast(care_site_id as bigint)1000 + @analysis_id as covariate_id,
c.care_site_name as covariate_name,
care_site_id as concept_id,
count() as sum_value,
count()1.0 / stat.total_cnt1.0 as average_value
from (select distinct care_site_id,
cohort.subject_id,
cohort.cohort_start_date
from @cohort_table cohort
inner join @cdm_database_schema.visit_occurrence on cohort.subject_id = visit_occurrence.person_id
where visit_start_date >= dateadd(d,0,cohort.cohort_start_date)
and care_site_id != 0
and cohort.cohort_definition_id = @cohort_id) visit_entries
join @cdm_database_schema.care_site c on visit_entries.care_site_id = c.care_site_id
cross join (select count() total_cnt
from @cohort_table
where cohort_definition_id = @cohort_id) stat
group by care_site_id,
c.care_site_name,
stat.total_cnt

Any help would be much appreciated!

-Ben Martin

#2

If you look at your WebAPI logs where yu saw the ‘BadSqlGrammar’, it should give additional information about the cause. Such as ‘relation does not exist’ or syntax error.

You can copy that sql statement and put it into a sql editor (after replacing the tokens) to try to run it. That’s what I usually do when I am writing custom SQL like that.

1 Like
#3

This was great advice, thank you Chris!

Replacing the tokens and running the query in MS SQL Server Studio allowed me to iterate through the errors. Then I just put the tokens back in, and it worked in ATLAS.

Here is the ATLAS version of the custom SQL feature analysis for counting visits by care_site_id for anyone else searching for something like this:

– Custom analysis producing visit counts by care_site_id
select cast(c.care_site_id as bigint)1000 + @analysis_id as covariate_id,
c.care_site_name as covariate_name,
c.care_site_id as concept_id,
count() as sum_value,
count()1.0 / stat.total_cnt1.0 as average_value
from (select distinct care_site_id,
cohort.subject_id,
cohort.cohort_start_date
from @cohort_table cohort
inner join @cdm_database_schema.visit_occurrence on cohort.subject_id = visit_occurrence.person_id
where visit_start_date = dateadd(d,0,cohort.cohort_start_date)
and care_site_id != 0
and cohort.cohort_definition_id = @cohort_id) visit_entries
join @cdm_database_schema.care_site c on visit_entries.care_site_id = c.care_site_id
cross join (select count() total_cnt
from @cohort_table
where cohort_definition_id = @cohort_id) stat
group by c.care_site_id,
c.care_site_name,
stat.total_cnt

Thanks again!
Ben Martin

t