BookmarkSubscribeRSS Feed
Harrymac
Calcite | Level 5

Question solved thanks everyone

30 REPLIES 30
Patrick
Opal | Level 21

What identifies the same company over several years?

Harrymac
Calcite | Level 5

permn number

Patrick
Opal | Level 21

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;

Harrymac
Calcite | Level 5

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

Harrymac
Calcite | Level 5

error.JPG

Patrick
Opal | Level 21

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.

Harrymac
Calcite | Level 5

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.

Harrymac
Calcite | Level 5

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

Patrick
Opal | Level 21

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

Harrymac
Calcite | Level 5

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?

Patrick
Opal | Level 21

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

Patrick
Opal | Level 21

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;

Harrymac
Calcite | Level 5

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

Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 30 replies
  • 1339 views
  • 0 likes
  • 4 in conversation