Help using Base SAS procedures

past observations

Reply
Occasional Contributor
Posts: 14

past observations

Question solved thanks everyone

Respected Advisor
Posts: 3,887

Re: past observations

What identifies the same company over several years?

Occasional Contributor
Posts: 14

Re: past observations

permn number

Respected Advisor
Posts: 3,887

Re: past observations

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;

Occasional Contributor
Posts: 14

Re: past observations

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

Occasional Contributor
Posts: 14

Re: past observations

error.JPG

Respected Advisor
Posts: 3,887

Re: past observations

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.

Occasional Contributor
Posts: 14

Re: past observations

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.

Occasional Contributor
Posts: 14

Re: past observations

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:error_testerdata.JPG

Respected Advisor
Posts: 3,887

Re: past observations

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.

Capture1.PNG

Capture2.PNG

Capture3.PNG

Capture4.PNG

Capture5.PNG

Capture7.PNG

Occasional Contributor
Posts: 14

Re: past observations

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?

Respected Advisor
Posts: 3,887

Re: past observations

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).

Respected Advisor
Posts: 3,887

Re: past observations

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;

Occasional Contributor
Posts: 14

Re: past observations

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 attachmentCapture.JPG

Respected Advisor
Posts: 3,887

Re: past observations

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

Capture.PNG

Ask a Question
Discussion stats
  • 30 replies
  • 557 views
  • 0 likes
  • 4 in conversation