update a field by removing quarter or removing month

[andy_wk]

Second try out.

This is an excerpt of my table. I have 58 files in total.

var1              periode adddate 
ei_isbu_m.csv      m      56 
namq_nace10_k.csv  q      123 
ei_cphi_m.csv      m      53 
ei_isrt_m.csv      m      15 
namq_aux_lp.csv    q      45 

var1 is the name of the files, periode is either q for quaterly and m for monthly and adddate is the number of days I'm adding to the current date.

What I want is the lastest publication date for each of the series and where the date is strictly inferior to my current date.

The publication date goes that way. For months, the publication date is the last month plus the adddate. For quaterly publication, it will be the end month of the quarter plus the add date.

Let's say we are using today's date is 12/01/2014

ei_isbu_m.csv latest publication date will be 25/02/2014 as the last day of last month is31/12/13.

var1              periode      adddate date_publi_1  
ei_isbu_m.csv      m            56      25/02/14 
namq_nace10_k.csv  q            123    03/05/14 
ei_cphi_m.csv      m            53      22/02/14 
ei_isrt_m.csv      m            15      15/01/14 
namq_aux_lp.csv    q            45      14/02/14 

However and as you can see, all the date of date_publi_1 are superior to the 12/01/14.

So I need to figure out the lastest publication date that goes before date_publi_1. Therefore for quaterly publication, I'll need to remove an additional quarter which , will give me the end of September 2013 (30/09/13). And for the monthly publication, the reference month will be the end of November (30/09/13).

var1              periode      adddate date_publi_1 
ei_isbu_m.csv      m            56 25/01/14 
namq_nace10_k.csv  q            123 31/01/14 
ei_cphi_m.csv      m 53 22/01/14 
ei_isrt_m.csv      m 15 15/12/13 
namq_aux_lp.csv    q 45 14/11/13 

As you can see again, there are still 3 files, for which the date is superior to my current date e.g. ei_isbu_m.csv , namq_nace10_k.csv and ei_cphi_m.csv.

I need to remove one extra quarter for the quarter, which will give me the end of June (30/06/13). For the month, I'll need to remove one extra month, which will give me the end of October (31/10/13).

But, I'll keep the date_publi_1 for ei_isrt_m.csv and namq_aux_lp.csv as they are now inferior to my current date

Therefore for quaterly publication, I'll need to remove an additional quarter which , will give me the end of September 2013 (30/09/13). And for the monthly publication, the reference month will be the end of November (30/09/13).

var1 periode adddate date_publi_1 
ei_isbu_m.csv m 56 26/12/13 
namq_nace10_k.csv q 123 31/10/13 
ei_cphi_m.csv m 53 23/12/13 
ei_isrt_m.csv m 15 15/12/13 
namq_aux_lp.csv q 45 14/11/13 

My script is below. It is not working the way I want and I know for sure it is too lengthy. Worse I'm using a macro but I don't know how to do that other than with a macro.

Sorry for that but I've been struggling for some days already.

Any insights or help would be more than welcome.

Plus : I need to get more familiar with SAS coding but I feel that macro is not THE way or at least not the only way. Any tips (books, videos, else) for familiarizing myself with SAS coding will be welcome.

/*beginning of script*/ 
    proc sql;
    select var1 into :nomficq separated by ' ' from correspondance where periode
    ="q";
    quit;

    proc sql;
    select ajoutdate into :ajoutdateq separated by ' ' from correspondance where periode
    ="q";
    quit;

    proc sql;
    select var1 into :nomficmne777 separated by ' ' from correspondance where periode
    ="m" and ajoutdate ne 777;
    quit;

    proc sql;
    select ajoutdate into :ajoutdatemne777 separated by ' ' from correspondance where periode
    ="m" and ajoutdate ne 777;
    quit;

    proc sql;
    select var1 into :nomficm separated by ' ' from correspondance where periode
    ="m" and ajoutdate eq 777;
    quit;

    proc sql;
    select ajoutdate into :ajoutdate separated by ' ' from correspondance where periode
    ="m" and ajoutdate ne 777;
    quit;

    %put nbfic: &nbfic ;
    %put nomficq: &nomficq;
    %put ajoutdateq: &ajoutdateq;
    %put nomficmne777: &nomficmne777;
    %put ajoutdatemne777: &ajoutdatemne777;
    %put nomficm: &nomficm;
    %put ajoutdate: &ajoutdate;

    data correspondance;
    set correspondance;
    %do i=1 %to &nbfic;
    if periode="q" and var1=&nomficq then do;
    dern_date_trim_3=intnx('quarter',intnx('month',date(),0,'E'),-4,'E');
    dern_date_publi_t_3=intnx('day',intnx('quarter',intnx('month',date(),0,'E'),-4,'E'),20);
    dern_date_trim_2=intnx('quarter',intnx('month',date(),0,'E'),-3,'E');
    dern_date_publi_t_2 =intnx('day',intnx('quarter',intnx('month',date(),0,'E'),-3,'E'),20);
    dern_date_trim_1=intnx('quarter',intnx('month',date(),0,'E'),-2,'E');
    dern_date_publi_t_1=intnx('day',intnx('quarter',intnx('month',date(),0,'E'),-2,'E'),20);
    dern_date_trim_0=intnx('quarter',intnx('month',date(),0,'E'),-1,'E');
    dern_date_publi_t_0=intnx('day',intnx('quarter',intnx('month',date(),0,'E'),-1,'E'),20);
    format dern_date_publi_t_0 dern_date_trim_0 dern_date_publi_t_1 dern_date_trim_1 dern_date_publi_t_2 dern_date_trim_2 dern_date_publi_t_3 dern_date_trim_3 dern_dispo ddmmyy10.;
    end;
    else if periode="m" and ajoutdate ne 777 and var1=&nomficm then do;
       dern_date_mois_3=intnx('month',intnx('month',date(),0,'E'),-4,'E');
       dern_date_publi_m_3 =intnx('day',intnx('month',intnx('month',date(),0,'E'),-4,'E'),&ajoutdatemne777);
       dern_date_mois_2=intnx('month',intnx('month',date(),0,'E'),-3,'E');
       dern_date_publi_m_2 =intnx('day',intnx('month',intnx('month',date(),0,'E'),-3,'E'),&ajoutdatemne777);
       dern_date_mois_1=intnx('month',intnx('month',date(),0,'E'),-2,'E');
       dern_date_publi_m_1 =intnx('day',intnx('month',intnx('month',date(),0,'E'),-2,'E'),&ajoutdatemne777);
       dern_date_mois_0=intnx('month',intnx('month',date(),0,'E'),-1,'E');
       dern_date_publi_m_0 =intnx('day',intnx('month',intnx('month',date(),0,'E'),-1,'E'),&ajoutdatemne777);
       format dern_date_mois_3 dern_date_publi_m_3 dern_date_mois_2 dern_date_publi_m_2 dern_date_mois_1 dern_date_publi_m_1 dern_date_mois_0 dern_date_publi_m_0 dern_dispo ddmmyy10.;
    end;
    else if periode="m" and ajoutdate eq 777 and var1=&nomficmne777 then do;
       dern_date_mois_a_3=intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-4);
       dern_date_publi_a_m_3 =intnx('day',intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-4),&ajoutdate);
       dern_date_mois_a_2=intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-3);
       dern_date_publi_a_m_2 =intnx('day',intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-3),&ajoutdate);
       dern_date_mois_a_1=intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-2);
       dern_date_publi_a_m_1 =intnx('day',intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-2),&ajoutdate);
       dern_date_mois_a_0=intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-1);
       dern_date_publi_a_m_0 =intnx('day',intnx('month',intnx('weekday',intnx('month',date(),0,'E'),-1),-1),&ajoutdate);
       format dern_date_mois_a_3 dern_date_publi_a_m_3 dern_date_mois_a_2 dern_date_publi_a_m_2 dern_date_mois_a_1 dern_date_publi_a_m_1 dern_date_mois_a_0 dern_date_publi_a_m_0 dern_dispo ddmmyy10.;
    end;

    if periode="q" and var1=&nomficq and dern_date_publi_t_0 < date()  then ;
    dern_dispo=dern_date_trim_0;
    else if periode="q" and var1=&nomficq and dern_date_publi_t_1 <  date()
    then dern_dispo=dern_date_trim_1;
    else if periode="q" and var1=&nomficq and dern_date_publi_t_2 <  date()
    then dern_dispo=dern_date_trim_2;
    else if periode="q" and var1=&nomficq and dern_date_publi_t_3  <  date() then dern_dispo=dern_date_trim_3;

    if periode="m" and ajoutdate ne 777 and var1=&nomficm and dern_date_publi_m_0  < date()  then ;
    dern_dispo=dern_date_mois_0;
    else if periode="m" and ajoutdate ne 777 and var1=&nomficq and dern_date_publi_m_1 <  date()
    then dern_dispo=dern_date_mois_1;
    else if periode="m" and ajoutdate ne 777 and var1=&nomficq and dern_date_publi_m_2 <  date()
    then dern_dispo=dern_date_mois_2;
    else if periode="m" and ajoutdate ne 777 and var1=&nomficq and dern_date_publi_m_3  <  date() then dern_dispo=dern_date_mois_3;

    if periode="m" and ajoutdate eq 777 and var1=&nomficm and dern_date_publi_a_m_0 < date()  then ;
    dern_dispo=dern_date_mois_a_0;
    else if periode="m" and ajoutdate eq 777 and var1=&nomficq and dern_date_publi_a_m_1 <  date()
    then dern_dispo=dern_date_mois_a_1;
    else if periode="m" and ajoutdate eq 777 and var1=&nomficq and dern_date_publi_a_m_2 <  date()
    then dern_dispo=dern_date_mois_a_2;
    else if periode="m" and ajoutdate eq 777 and var1=&nomficq and dern_date_publi_a_m_3  <  date() then dern_dispo=dern_date_mois_a_3;
    %end;
    run;

    %mend;

    %corresp;

/*end of script*/

[RW9]

Hi,

Not really getting this as there seems to be quite a few questions and bits in there.  Maybe try breaking it down into individual steps, you can always refactor later into a smaller program.  Here is what I gathered from your requirements to find latest date:

data have;
  attrib var1 periode format=$200. adddate format=best.;
  infile datalines;
  input var1 $ periode $ adddate;
datalines;
ei_isbu_m.csv m 56        
namq_nace10_k.csv q 123
ei_cphi_m.csv m 53
ei_isrt_m.csv m 15
namq_aux_lp.csv q 45
;
run;
data step1;
  set have;
  attrib date_publi_1 format=date9.;
  if periode="m" then date_publi_1=intnx('MONTH','12JAN2014'd,adddate,'SAME');
  else date_publi_1=intnx('QTR','12JAN2014'd,adddate);
run;

proc sort data=step1;
  by descending adddate;
run;

data want;
  set step1 (obs=1);
run;

[andy_wk]

Hi RW9,

Thanks for your feedback. I think I can do it.

Step by step but I definitely can.

Best,

Andy