Calculating date variable

[lioradam]

Hello,

I have the following variables, PRD_MON and PRD_YR

PRD_MON	PRD_YR
12	2014
6	2018
8	2015

and I want to calculate the variable "Test period" which equates to the date of PRD_MON and PRD_YR minus 9 months as in this example:

PRD_MON	PRD_YR	Test period
12	2014	3, 31, 2014
6	2018	9, 30,2017
8	2015	11,30,2014

What code can I use?

Thank you,

Lior

[PaigeMiller · Accepted solution]

data have;
    input prd_mon prd_yr;
cards;
12 2014
6 2018
8 2015
;
data want;
    set have;
    date=mdy(prd_mon,1,prd_yr);
    test_period=intnx('month',date,-9,'e');
    format test_period mmddyy10.;
run;

With regards to providing us with sample data, @lioradam , please note and follow my lead in the future, the data is provided via working SAS data step code, and not just by showing us a list. 

[lioradam]

Thank you for the code.

Could you please explain how to provide data via the working SAS data step code?

Regards,

Lior

[PaigeMiller]

I have a DATA step in my code that produces a SAS data set to work from. We can only produce code that works on SAS data sets. You need to do this as well, provide use with code to create the SAS data set, instead of just listing data values.

[lioradam]

I work with SAS Enterprise Guide. I import data from external databases 

and then work with the tools of Enterprise Guide (Query Builder, Summary Statistic, etc.) Occasionally I add to the Process Flow  Programs in which I write the code myself.

I can add the Preview of the Query Builder in which I import the data, like the code attached here.

Is that helpful?

%_eg_conditional_dropds(WORK.QUERY_FOR_QHA4MWKGUYYQJA54R);

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_QHA4MWKGUYYQJA54R AS 
   SELECT DISTINCT t1.TICKER, 
          t1.ACT_STD, 
          t1.ACTION, 
          t1.ACTTIMS, 
          t1.ANNDATS, 
          t1.ANNTIMS, 
          t1.CURR, 
          t1.DIFF_CODE, 
          t1.EEFYMO, 
          t1.GUIDANCE_CODE, 
          t1.MEAN_AT_DATE, 
          t1.MEASURE, 
          t1.MOD_DATE, 
          t1.MOD_TIME, 
          t1.ACTDATS, 
          t1.PDICITY, 
          t1.PRD_MON, 
          t1.PRD_YR, 
          t1.RANGE_DESC, 
          t1.UNITS, 
          t1.USFIRM, 
          t1.VAL_1, 
          t1.VAL_2, 
          /* value_forcast */
            (t1.VAL_1) AS value_forcast
      FROM TMP00001.qha4mwkguyyqja54r t1
      WHERE t1.MEASURE IN ('EPS')  AND t1.PDICITY IN ('ANN' ) 
      ORDER BY t1.TICKER,
               t1.PRD_YR,
               t1.PRD_MON,
               t1.ACTDATS;
QUIT;

[Patrick]

@lioradam Many questions are best answered via working code. To create such code we need some representative data to work with so we can actually test the code.

What many people in this forum appreciate is if the one asking the question provides such data in the form of a working SAS data step and not just as some copy/pasted table or even worse via a screen-shot. If you provide already the data step then the ones providing the answer don't need first to spend time creating the sample data.

The SQL you posted wouldn't create such sample data. 

If you don't know how to write such a simple data step then you could copy/paste data as per your initial question into an Excel and then use EG to import this Excel selecting to include the data. This would generate code that you could post here as it includes the data.

If posting code please use the running man icon 

[lioradam]

Hi Patrick,

Thank you for your explanation.

I will follow your suggested method to add data next time I will approach this forum.

Regards,

Lior

[PaigeMiller]

Not acceptable. We do not have access to your databases, your SQL does not work for us. Here is a link to a SAS macro that will take the extract from your SQL and provide working SAS data step code.

[lioradam]

Thank you.

Lior