BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Irenelee
Quartz | Level 8

How to make the firm(PERMNO) not classified into Minhttps://drive.google.com/file/d/1grx_6p-yVMVzrzvVjdtfwyFUjRpq10W6/view?usp=sharing  again within one month? Thx!

data MK (drop=_:);
  set HL;
  by permno;

  retain _first_date_qualified . 
         _H   .                 
         _H_date .              
         _L   .                 
         _L_date .              
          PORTFOLIO .
;             
LENGTH  PORTFOLIO $11;

  if first.permno then do;
    call missing(of _:);

_first_date_qualified=intnx('year',date,1,'same')+1;

 if date < _first_date_qualified then do;
    portfolio = 'Unqualified';

	END;
 
  if _H=. then _H=P;
  if _L=. then _L=P;

  if P ^=. then do;
    if P > _H AND NOT (PORTFOLIO='MAX' AND DATE < INTNX('MONTH', _H_DATE, 1, 'SAME'))  then do;
      _H=P;
      _H_date=date;
      portfolio='Max';
    end;

    else if P < _L AND NOT (PORTFOLIO='MIN' AND DATE < INTNX('MONTH', _L_DATE, 1, 'SAME')) then do;
      _L=P;
      _L_date=date;
      portfolio='Min';
    end;

    else portfolio='Comparison';
  end;

  if date >= _first_date_qualified then do;
    H=_H;
    H_date=_H_date;
    L=_L;
    L_date=_L_date;
  end;

  format H_date L_date yymmdd10. ;

run;

10000 1987-03-02 3893 1 4.4375 0.375 0.375 Min 1986-01-27 1987-03-02
10000 1987-03-03 3893 1 4.4375 0.375 0.375 Comparison 1986-01-27 1987-03-02
10000 1987-03-04 3893 1 4.4375 0.375 0.375 Comparison 1986-01-27 1987-03-02
10000 1987-03-05 3893 1 4.4375 0.359375 0.359375 Min 1986-01-27 1987-03-05
10000 1987-03-06 3893 1 4.4375 0.359375 0.359375 Comparison 1986-01-27 1987-03-05
10000 1987-03-09 3893 1 4.4375 0.359375 0.359375 Comparison 1986-01-27 1987-03-05
10000 1987-03-10 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-11 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-12 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-13 3893 1 4.4375 0.359375 0.40625 Comparison 1986-01-27 1987-03-05
10000 1987-03-16 3893 1 4.4375 0.359375 0.40625 Comparison 1986-01-27 1987-03-05
10000 1987-03-17 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-18 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-19 3893 1 4.4375 0.34375 0.34375 Min 1986-01-27 1987-03-19
10000 1987-03-20 3893 1 4.4375 0.34375 0.34375 Comparison 1986-01-27 1987-03-19
10000 1987-03-23 3893 1 4.4375 0.34375 0.34375 Comparison 1986-01-27 1987-03-19
10000 1987-03-24 3893 1 4.4375 0.328125 0.328125 Min 1986-01-27 1987-03-24
10000 1987-03-25 3893 1 4.4375 0.328125 0.34375 Comparison 1986-01-27 1987-03-24
10000 1987-03-26 3893 1 4.4375 0.28125 0.28125 Min 1986-01-27 1987-03-26
10000 1987-03-27 3893 1 4.4375 0.28125 0.28125 Comparison 1986-01-27 1987-03-26
10000 1987-03-30 3893 1 4.4375 0.28125 0.28125 Comparison 1986-01-27 1987-03-26
10000 1987-03-31 3893 1 4.4375 0.25 0.25 Min 1986-01-27 1987-03-31
10000 1987-04-01 3893 1 4.4375 0.234375 0.234375 Min 1986-01-27 1987-04-01
10000 1987-04-02 3893 1 4.4375 0.234375 0.234375 Comparison 1986-01-27 1987-04-01

1 ACCEPTED SOLUTION

Accepted Solutions
Irenelee
Quartz | Level 8

Got it now! thx!

DM'LOG; CLEAR; OUT; CLEAR; ODSRESULTS; CLEAR;';
%LET FOLDER=%STR(C:\USERS\ALAIN\ONEDRIVE\桌面\HIGH);
LIBNAME HIGH "&FOLDER";

DATA HH;
 SET HIGH.HH;
 IF CFACPR=0 THEN DELETE;
RUN;

DATA HH;
	SET	HH;
	IF _N_ <=1000;
RUN;

PROC SORT 
  DATA=HH (KEEP  =SHRCD EXCHCD PERMNO DATE PRC CFACPR SHROUT CFACSHR
                      WHERE =( SHRCD IN (10,11) AND EXCHCD IN (1,2,3,31,32,33) )
                      ) 

OUT=HL(DROP=SHRCD EXCHCD );
  BY PERMNO DATE;
RUN;

DATA HL; 
    SET HL; 
    BY PERMNO DATE; 
    RETAIN H L 0; 
    P=ABS(DIVIDE (PRC, CFACPR));
    IF FIRST.PERMNO THEN H = P; 
    IF FIRST.PERMNO THEN L = P;
    H = MAX(H, P); 
    L = MIN(L, P);
 DROP  PRC CFACPR;
   RUN; /* PERMNO DATE  SHROUT CFACSHR H L P */

data MK (drop=_:);
  set HL;
  BY PERMNO date; 

  retain _first_date_qualified . 
         _H   .                 
         _H_date .              
         _L   .                 
         _L_date .              
          PORTFOLIO 
;             
LENGTH  PORTFOLIO $11;

  if first.permno then do;
    call missing(of _:);
    _first_date_qualified=intnx('year',date,1,'same')+1;
  end;

  if _H=. then _H=P;
  if _L=. then _L=P;

  if P ^=. then do;
    if P > _H and date >= INTNX('MONTH', _H_DATE, 1, 'SAME')) then do;
      _H=P;
      _H_date=date;
      portfolio='Max';
    end;

    else if P < _L and date >= INTNX('MONTH', _L_DATE, 1, 'SAME')) then do;
      _L=P;
      _L_date=date;
      portfolio='Min';
    end;

    else portfolio='Comparison';
  end;

  if date >= _first_date_qualified then do;
      H = _H;
      H_date = _H_date;
      L = _L;
      L_date = _L_date;
  end;

  if date < _first_date_qualified then do;
    portfolio = 'Unqualified';
  end;

  format H_date L_date yymmdd10. ;

run;

DATA MK;
  SET MK(OBS=500);
  PUT (_ALL_) (+0);
RUN;

View solution in original post

2 REPLIES 2
Irenelee
Quartz | Level 8

Got it now! thx!

DM'LOG; CLEAR; OUT; CLEAR; ODSRESULTS; CLEAR;';
%LET FOLDER=%STR(C:\USERS\ALAIN\ONEDRIVE\桌面\HIGH);
LIBNAME HIGH "&FOLDER";

DATA HH;
 SET HIGH.HH;
 IF CFACPR=0 THEN DELETE;
RUN;

DATA HH;
	SET	HH;
	IF _N_ <=1000;
RUN;

PROC SORT 
  DATA=HH (KEEP  =SHRCD EXCHCD PERMNO DATE PRC CFACPR SHROUT CFACSHR
                      WHERE =( SHRCD IN (10,11) AND EXCHCD IN (1,2,3,31,32,33) )
                      ) 

OUT=HL(DROP=SHRCD EXCHCD );
  BY PERMNO DATE;
RUN;

DATA HL; 
    SET HL; 
    BY PERMNO DATE; 
    RETAIN H L 0; 
    P=ABS(DIVIDE (PRC, CFACPR));
    IF FIRST.PERMNO THEN H = P; 
    IF FIRST.PERMNO THEN L = P;
    H = MAX(H, P); 
    L = MIN(L, P);
 DROP  PRC CFACPR;
   RUN; /* PERMNO DATE  SHROUT CFACSHR H L P */

data MK (drop=_:);
  set HL;
  BY PERMNO date; 

  retain _first_date_qualified . 
         _H   .                 
         _H_date .              
         _L   .                 
         _L_date .              
          PORTFOLIO 
;             
LENGTH  PORTFOLIO $11;

  if first.permno then do;
    call missing(of _:);
    _first_date_qualified=intnx('year',date,1,'same')+1;
  end;

  if _H=. then _H=P;
  if _L=. then _L=P;

  if P ^=. then do;
    if P > _H and date >= INTNX('MONTH', _H_DATE, 1, 'SAME')) then do;
      _H=P;
      _H_date=date;
      portfolio='Max';
    end;

    else if P < _L and date >= INTNX('MONTH', _L_DATE, 1, 'SAME')) then do;
      _L=P;
      _L_date=date;
      portfolio='Min';
    end;

    else portfolio='Comparison';
  end;

  if date >= _first_date_qualified then do;
      H = _H;
      H_date = _H_date;
      L = _L;
      L_date = _L_date;
  end;

  if date < _first_date_qualified then do;
    portfolio = 'Unqualified';
  end;

  format H_date L_date yymmdd10. ;

run;

DATA MK;
  SET MK(OBS=500);
  PUT (_ALL_) (+0);
RUN;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 291 views
  • 1 like
  • 2 in conversation