topic Re: past observations in SAS Procedures

past observations

Harrymac — Sun, 09 Sep 2012 03:07:20 GMT

Question solved thanks everyone

Re: past observations

Patrick — Sun, 09 Sep 2012 06:10:15 GMT

What identifies the same company over several years?

Re: past observations

Harrymac — Sun, 09 Sep 2012 06:15:54 GMT

permn number

Re: past observations

Patrick — Sun, 09 Sep 2012 06:22:56 GMT

proc sql;

create table want as

select

l.permno

,l.date

,r.EARN

,r.DEP

,r.ME

,(r.EARN+r.DEP)/r.ME as CF1

,sum(r.EARN,r.DEP)/r.ME as CF2

from tester l,tester r

where l.permno=r.permno and intnx('month',l.date,0,'e')=intnx('month',r.date,6,'e') and month(l.date)=6

order by l.permno, l.date

;

quit;

Re: past observations

Harrymac — Sun, 09 Sep 2012 07:18:35 GMT

Thanks is there something missing from the above code? I have received an error message (see attached).

Re: past observations

Harrymac — Sun, 09 Sep 2012 07:19:03 GMT

Re: past observations

Patrick — Sun, 09 Sep 2012 08:00:50 GMT

The code worked fine with your "tester" sample data.

What you're getting is not an ERROR but a NOTE.

The one about the SUM() function: You have missings values both for "dep" and "me". The note is just telling you that you've got such missings. If using a SUM() function then you get only a missing as result if all variables are missing, where with a sum operator you'll get a missing if any one of the oparands are missing. That's why I've created CF1 and CF2 for you so that you can choose.

About note for INTNX(): It appears that you've got invalid SAS date values in your real data. That is a data quality issue and not a coding issue. You won't see this message if running the code on your "tester" data sample.

Re: past observations

Harrymac — Sun, 09 Sep 2012 08:43:58 GMT

If I wanted to change to code to recognise dates as integers instead of SAS date values. How do I do I edit the code? As a lot of the dates I address are numbers not sas dates. Thanks for your input.

Re: past observations

Harrymac — Sun, 09 Sep 2012 09:00:18 GMT

Seems like the intnx is causing a similar problem with my tester data, I just used the code on the sample submitted above and I received the following message:

Re: past observations

Patrick — Sun, 09 Sep 2012 09:48:45 GMT

Then you must be doing something wrong when converting the date string into a SAS date value for variable "date". It works for me as demonstrated below.

Re: past observations

Harrymac — Sun, 09 Sep 2012 10:11:57 GMT

What version of SAS do you use? I am using 9.2. I click file and import and I don't receive the prompts to define field attributes. Hm this is worrisome, is it not possible to alter the code to account for dates as integers?

Re: past observations

Patrick — Sun, 09 Sep 2012 10:11:58 GMT

"If I wanted to change to code to recognise dates as integers instead of SAS date values. How do I do I edit the code? As a lot of the dates I address are numbers not sas dates. Thanks for your input."

SAS has exactly 2 data types: Numeric and Character. A SAS date value is stored in a numeric variable. A SAS date value is the number of days since 1 January 1960. In storing dates this way one can easily handle them (eg. if you want the date a day later you simply add +1 to your variable). There are also a number of calendar functions which allow for simple date handling (eg. same date of month 6 months later: intnx('month',date,6,'s') ). You find all these functions in the Base SAS documentation.

It's a bad idea to store dates as numbers like 20120522 as this doesn't allow you to do all these calculations and to use all these ready made calendar functions. What you do is apply a format to the numeric SAS variable containing a SAS date value (days since 1Jan1960). So if you want to look at your date in the form YYYYMMDD you just apply "format date yymmddn8.". All these formats are also documented in the Base SAS reference doc under Formats.

It's really worth to read up on how SAS deals with dates and it's the way you should do it. Every other way is sub-optimal and you will understand once you get how SAS handles dates. And by the way: It's not only SAS dealing in this way with dates - every programing language/database I know does it in a similar manner.

And just to add to the above:

Using a SAS date value it's really simple to select a date 6 months ago as I've done it the SQL I've posted to solve your problem. This would have been quite a bit harder when using a date string (no matter if this date string is now stored as an integer in a numeric variable or as a string in a character variable).

Re: past observations

Patrick — Sun, 09 Sep 2012 10:31:26 GMT

I've done it with SAS9.3 and EG5.1. The oldest EG version I currently can access is EG4.3. Is this your version as well or are you on an earlier version?

You have only strings in a csv file. What you must do is to provide a INFORMAT to SAS which tell it how to read this string. For your date strings in the source this informat is YYMMDD8. You then also want to see something else than a pure number when looking at the dates in the resulting SAS table so you should also apply a FORMAT to the SAS variable. This could be YYMMDD10. or DATE9. (or anything else possible according to your taste).

Re: past observations

Patrick — Sun, 09 Sep 2012 10:41:49 GMT

Here the code EG created for me with some amendments so you can run it from any editor. Just change the path to the 'tester.csv' or use your real data and it should work. I've also added the SQL at the end of the code.

Currently only the string for "date" gets converted into a SAS date. You could change the informats so that also things like "MONTHDATE" would get converted to a SAS date value.... But once you understand how SAS dates work you'll see that this is actually duplicated information and that having a single variable "date" is all you need (besides of "datadate" which seems to be a technical variable telling you when the data was retrieved/loaded).

%let csvFile=C:\temp\tester.csv;

DATA WORK.tester;
    LENGTH
        dateff             8
        mktrf              8
        smb                8
        hml                8
        rf                 8
        umd                8
        MONTHDATE          8
        DATE               8
        HSICMG             8
        PERMNO             8
        SHROUT             8
        PRC                8
        RET              $ 9
        RETX             $ 9
        vwretd             8
        vwretx             8
        ewretd             8
        ewretx             8
        year               8
        fyear              8
        gvkey              8
        lpermno            8
        datadate           8
        indfmt           $ 4
        consol           $ 1
        popsrc           $ 1
        datafmt          $ 3
        CURCD            $ 3
        ACT                8
        AT                 8
        BKVLPS             8
        CH                 8
        DEPC             $ 1
        DLC                8
        DLTT               8
        DVT                8
        EBIT               8
        EBITDA             8
        EPSPI              8
        REVT               8
        TXT                8
        XDP                8
        XINT               8
        COSTAT           $ 1
        earn               8
        dep                8
        me                 8
        MONTH              8 ;
    FORMAT
        dateff           YYMMDD10.
        mktrf            BEST7.
        smb              BEST7.
        hml              BEST7.
        rf               BEST6.
        umd              BEST7.
        MONTHDATE        BEST6.
        DATE             YYMMDD10.
        HSICMG           BEST2.
        PERMNO           BEST5.
        SHROUT           BEST5.
        PRC              BEST8.
        RET              $CHAR9.
        RETX             $CHAR9.
        vwretd           BEST7.
        vwretx           BEST7.
        ewretd           BEST7.
        ewretx           BEST7.
        year             BEST4.
        fyear            BEST4.
        gvkey            BEST5.
        lpermno          BEST5.
        datadate         YYMMDD10.
        indfmt           $CHAR4.
        consol           $CHAR1.
        popsrc           $CHAR1.
        datafmt          $CHAR3.
        CURCD            $CHAR3.
        ACT              BEST8.
        AT               BEST9.
        BKVLPS           BEST8.
        CH               BEST8.
        DEPC             $CHAR1.
        DLC              BEST7.
        DLTT             BEST8.
        DVT              BEST7.
        EBIT             BEST8.
        EBITDA           BEST8.
        EPSPI            BEST6.
        REVT             BEST9.
        TXT              BEST7.
        XDP              BEST7.
        XINT             BEST7.
        COSTAT           $CHAR1.
        earn             BEST10.
        dep              BEST7.
        me               BEST12.
        MONTH            BEST2. ;
    INFORMAT
        dateff           YYMMDD10.
        mktrf            BEST7.
        smb              BEST7.
        hml              BEST7.
        rf               BEST6.
        umd              BEST7.
        MONTHDATE        BEST6.
        DATE             YYMMDD10.
        HSICMG           BEST2.
        PERMNO           BEST5.
        SHROUT           BEST5.
        PRC              BEST8.
        RET              $CHAR9.
        RETX             $CHAR9.
        vwretd           BEST7.
        vwretx           BEST7.
        ewretd           BEST7.
        ewretx           BEST7.
        year             BEST4.
        fyear            BEST4.
        gvkey            BEST5.
        lpermno          BEST5.
        datadate         YYMMDD10.
        indfmt           $CHAR4.
        consol           $CHAR1.
        popsrc           $CHAR1.
        datafmt          $CHAR3.
        CURCD            $CHAR3.
        ACT              BEST8.
        AT               BEST9.
        BKVLPS           BEST8.
        CH               BEST8.
        DEPC             $CHAR1.
        DLC              BEST7.
        DLTT             BEST8.
        DVT              BEST7.
        EBIT             BEST8.
        EBITDA           BEST8.
        EPSPI            BEST6.
        REVT             BEST9.
        TXT              BEST7.
        XDP              BEST7.
        XINT             BEST7.
        COSTAT           $CHAR1.
        earn             BEST10.
        dep              BEST7.
        me               BEST12.
        MONTH            BEST2. ;
    INFILE "&csvFile"
        LRECL=1000
        TERMSTR=CRLF
        DLM=','
        truncover
        DSD
        firstobs=2;
    INPUT
        dateff           : ?? YYMMDD8.
        mktrf            : ?? COMMA7.
        smb              : ?? COMMA7.
        hml              : ?? COMMA7.
        rf               : ?? COMMA6.
        umd              : ?? COMMA7.
        MONTHDATE        : ?? BEST6.
        DATE             : ?? YYMMDD8.
        HSICMG           : ?? BEST2.
        PERMNO           : ?? BEST5.
        SHROUT           : ?? BEST5.
        PRC              : ?? COMMA8.
        RET              : $CHAR9.
        RETX             : $CHAR9.
        vwretd           : ?? COMMA7.
        vwretx           : ?? COMMA7.
        ewretd           : ?? COMMA7.
        ewretx           : ?? COMMA7.
        year             : ?? BEST4.
        fyear            : ?? BEST4.
        gvkey            : ?? BEST5.
        lpermno          : ?? BEST5.
        datadate         : ?? YYMMDD8.
        indfmt           : $CHAR4.
        consol           : $CHAR1.
        popsrc           : $CHAR1.
        datafmt          : $CHAR3.
        CURCD            : $CHAR3.
        ACT              : ?? COMMA8.
        AT               : ?? COMMA9.
        BKVLPS           : ?? COMMA8.
        CH               : ?? COMMA8.
        DEPC             : $CHAR1.
        DLC              : ?? COMMA7.
        DLTT             : ?? COMMA8.
        DVT              : ?? COMMA7.
        EBIT             : ?? COMMA8.
        EBITDA           : ?? COMMA8.
        EPSPI            : ?? COMMA6.
        REVT             : ?? COMMA9.
        TXT              : ?? COMMA7.
        XDP              : ?? COMMA7.
        XINT             : ?? COMMA7.
        COSTAT           : $CHAR1.
        earn             : ?? COMMA10.
        dep              : ?? COMMA7.
        me               : ?? COMMA12.
        MONTH            : ?? BEST2. ;
RUN;

proc sql;
create table want as
select
    l.permno
    ,l.date
    ,r.EARN
    ,r.DEP
    ,r.ME
    ,(r.EARN+r.DEP)/r.ME as CF1
    ,sum(r.EARN,r.DEP)/r.ME as CF2
from tester l,tester r
    where l.permno=r.permno and intnx('month',l.date,0,'e')=intnx('month',r.date,6,'e') and month(l.date)=6
order by l.permno, l.date
;
quit;

Re: past observations

Harrymac — Sun, 09 Sep 2012 11:05:23 GMT

If I save my data as a DBF file can we avoid the data strings/SAS dates and use integers as dates for the code above? I thought the calculation of CF in June whilst using last years DEC variables wouldn't be so difficult haha. Sigh,

I followed your directions above, I was able to assemble the "want" database but I had blanks for CF1 CF2 and ME. Please see attachment

Re: past observations

Patrick — Sun, 09 Sep 2012 11:21:59 GMT

Not really sure what you've done. I just copied now the code I've posted (the one with the data step) and run it 1:1 in my environment in a new session using the data you've provided in tester.csv. That's what I get and what you should get using SAS9.2 and the tester.csv

Re: past observations

Patrick — Sun, 09 Sep 2012 11:32:36 GMT

Just use the code I've provided together with a .csv and it will work!

Please read my comments about SAS dates and forget about this integer stuff. It IS a bad idea and the wrong approach. It's simply not how one needs to deal with dates (and if people would have done it this way then there would never have been a Y2K issue - that's why the Mac OS didn't have to care ever about Y2K).

Re: past observations

Harrymac — Sun, 09 Sep 2012 11:33:02 GMT

I think this problem vests with the different SAS platforms in use. When I copy and paste your code above and my variables adopt some pretty "funky" values (see attachment).

Any readers of this thread if you could please assist with the original question posted, I am looking to create a code to obtain the variable below, with using integer dates, not sas dates.

(June T0) CF= (EARN+DEP)/ME (INPUTS ON THE RIGHT HAND SIDE ARE FOR DEC T-1)

Re: past observations

Harrymac — Sun, 09 Sep 2012 11:35:36 GMT

I just read this sorry. OK so SAS dates are the way to go, I still can't narrow down the problem in my tester data.

Re: past observations

Patrick — Sun, 09 Sep 2012 11:42:42 GMT

What I don't understand is why you should get different results running "my" code with the identical "tester.csv" data. I've even run this code using PC SAS 9.2 and I'm still getting the results I've posted. So you MUST do something different.

What I can see in the SAS table snapshot you've posted is that it looks as if there aren't the right data in the columns. This could happen if you wouldn't use 'truncover' or 'dsd' in the infile statement or if your source data would contain commas and text data is not quoted. But as I believe we're using the same code and the exactly same "texter.csv" this can't really be the explanation. Are we using identical code and source data?

By the way: What OS are you using? Mine is Win7. In case you're using some UNIX/Linux environment then try running "my code" without the following infile option "TERMSTR=CRLF".