Update a table conditional to month of date and casting two first letters with year of date

[msf2021]

Hi!

I have the following table : 

proc sql;
create table campanhas
(ID_CAMPANHA char(3),
TIPO char(20),
DATA_CAMP num informat=DDMMYY10.
format=DDMMYY10.
);

insert into campanhas
values('CA1','VENDA','05FEB12'd)
values('CA2','VENDA','04FEB12'd)
values('CA3','PÓS-VENDA','02OCT09'd)
values('CA4','VENDA','06MAY10'd)
values('CA5','PÓS-VENDA','30NOV10'd)
values('CA6','PÓS-VENDA','07FEB15'd);
quit;

Then i added a new column to this table : 

proc sql;
alter table campanhas
add Semestre char(6);
quit;

And now i want to update this new "Semester" column with the following case when condition : 

Considering two distinct semesters - the first being SS (Spring-Summer) and the second AW(Autumn - Winter) , the first two characters will be 'SS' or 'OI' and the next four will be the year (For example SS2020 for dates between 03/01/2020 and 31/08/2020)

Can anyone help me? 

Thanks

[Tom]

Much easier to use SAS code than SQL.

First let's create your data:

data have ;
  input ID_CAMPANHA :$3. TIPO :$20. DATA_CAMP :date.;
  format DATA_CAMP YYMMDD10. ;
cards;
A1  VENDA     05FEB2012
CA2 VENDA     04FEB2012
CA3 PÓS-VENDA 02OCT2009
CA4 VENDA     06MAY2010
CA5 PÓS-VENDA 30NOV2010
CA6 PÓS-VENDA 07FEB2015
;

Now let's make a NEW dataset (so we don't accidentally destroy our inputs) and define your new variable.

You could just test the MONTH to find the dates between MAR and AUG.

data want;
  set have;
  length semester $6 ;
  if 3 <= month(data_camp) <= 8 then semester=cats('SS',year(data_camp));
  else semester=cats('AW',year(data_camp));
run;

Results

Obs    ID_CAMPANHA    TIPO          DATA_CAMP    semester

 1         A1         VENDA        2012-02-05     AW2012
 2         CA2        VENDA        2012-02-04     AW2012
 3         CA3        PÓS-VENDA    2009-10-02     AW2009
 4         CA4        VENDA        2010-05-06     SS2010
 5         CA5        PÓS-VENDA    2010-11-30     AW2010
 6         CA6        PÓS-VENDA    2015-02-07     AW2015

If you did want to use SQL you should still make a NEW dataset and not destroy your source data.

proc sql;
create table want as
select a.*
     , case when (3 <= month(data_camp) <= 8) then cats('SS',year(data_camp))
            else cats('AW',year(data_camp))
       end as semester length=6 
from have a
;
quit;

[msf2021]

the thing is I have to make only one query and i have to use "update" (so with SQL) that's my difficulty

[Tom · Accepted solution]

---

@msf2021 wrote:

the thing is I have to make only one query and i have to use "update" (so with SQL) that's my difficulty

---

You need to explain more about why that would be the case.  If you are running SAS then use SAS.  If you have to run the SQL in some non SAS setting then the syntax that works in SAS might not work there as each SQL implementation is its own language.  The CREATE TABLE statement is a single "query".

Anyway, here is the SQL syntax to overwrite the SEMESTER variable in a dataset named WANT by using the UPDATE statement.

proc sql;
update want
set semester=
    case when (3 <= month(data_camp) <= 8) then cats('SS',year(data_camp))
        else cats('AW',year(data_camp))
    end
;
quit;

[msf2021]

Thank you!! 

[ballardw]

If you are working in SAS then you may not need additional variable. A custom format can display just about anything you want.

The following creates a data set to assign what I understand to be your desired text as the format value for date ranges and uses proc format to with that data set to create the format.

data example;
   start='01MAR2000'd;
   FMTName='Semestre';
   do while (start Le '01MAR2100'd);
      end= intnx('month',start,5,'e');
      if month(start) =3 then label=cats('SS',year(start));
      else label=cats('OI',year(start));
      output;
      start= intnx('month',start,6);
   end;
   format start end date9.;
run;

proc format cntlin=example;
run;

And example using it:

Proc print data=campanhas;
   format data_camp semestre.;
run;

This may have significant impact on ease of reporting or graphing if you want values to sort easily.

For most purposes the formatted values is display in calendar order.  If you add a character valued variable then you may see reports group all of the OI and then the SS together and additional work will be needed to display them in calendar order.

Groups created by formats are honored by almost all of the SAS procedures.