Reputation: 5044
update a field by removing quarter or removing month
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 is
31/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*/
Upvotes: 0
Views: 107
Answers (1)
Reputation: 6378
I think this may be what you want (or close):
data have;
input var1 $18. periode $7. adddate;
cards;
ei_isbu_m.csv month 56
namq_nace10_k.csv quarter 123
ei_cphi_m.csv month 53
ei_isrt_m.csv month 15
namq_aux_lp.csv quarter 45
;
run;
data want;
set have;
today="12Jan2014"d;
do i=1 by 1 until (date_publ<today);
date_publ=intnx(periode,today,-1*i,'E')+adddate;
end;
drop i;
format date_publ today date9.;
run;
proc print;
run;
Returns:
var1 periode adddate today date_publ
ei_isbu_m.csv month 56 12JAN2014 26DEC2013
namq_nace10_k.csv quarter 123 12JAN2014 31OCT2013
ei_cphi_m.csv month 53 12JAN2014 23DEC2013
ei_isrt_m.csv month 15 12JAN2014 15DEC2013
namq_aux_lp.csv quarter 45 12JAN2014 14NOV2013
Upvotes: 0
Related Questions
- Subtracting month in SAS
- How to transform specific time entry into month in SAS
- Assign columname (month) as value to another column SAS
- Add month to quarter date
- change all values in a date column to the end of the month
- SAS dates: format the quarter as year/quarter
- SAS Date and substracting 1 Month
- Adding a zero to my month function
- How to convert date value from day to Quarter in SAS
- Customize quarter month range