Here the example data

[ed_sas_member · Accepted solution]

Hi @Gretaku 

No panic, we are almost there 🙂

It comes from the fact that you use the same name for all your tables:

proc sql;

create table TMP.SAS_Zum_Tableau_aktualisierenBIS as

select *,

  year(date) as year,

  month(date) as month,

  avg(NPS_value) as monthly_avg

from TMP.SAS_ZUM_TABLEAU_AKTUALISIEREN

group by touchpoint_spec, month(date), year(date);

run;

/* average of value per month previous year */

proc sql;

create table TMP.SAS_Zum_Tableau_aktualisierenTER as

select distinct touchpoint_spec, year, month, monthly_avg

from TMP.SAS_Zum_Tableau_aktualisierenBIS;

quit;

proc sql;

create table TMP.SAS_Zum_Tableau_aktualisierenWANT as

select a.*, b.monthly_avg as monthly_avg_preyear

from TMP.SAS_Zum_Tableau_aktualisierenBIS as a left join TMP.SAS_Zum_Tableau_aktualisierenTER as b

on a.touchpoint_spec=b.touchpoint_spec and a.month = b.month and (a.year-1)=b.year

order by a.touchpoint_spec;

run;

[Gretaku]

@ed_sas_member 

Thank you so much! I really appreciate your great support with this!!! 

Have a lovely weekend.

Best

Gretaku

[ed_sas_member]

Awesome!

Thank you for your patience @Gretaku 

Have a lovely weekend too