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
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;
Please write the proper DATA step code around your example data so we know which value belongs to which variable.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.