Home

Irenelee

thx! How to create a column which is market value of last month 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 MV; SET MK; BY PERMNO date; Retain MV Last_Month_End; Last_Month_End = INTNX('MONTH', DATE, -1, 'end'); format Last_Month_End yymmdd10. ; MV = P * (SHROUT*CFACSHR) ; RUN; DATA LMV; SET MV; BY PERMNO DATE; RETAIN LMV 0; IF FIRST.PERMNO THEN LMV = MV; IF LAG(DATE)=Last_Month_End THEN LMV = LAG(MV); ELSE LMV = LAG(MV); RUN; DATA L MV; SET LMV(OBS=500); PUT (_ALL_) (+0); RUN; It's not as expected now.. 10000 1986-01-07 3680 1 2.5625 2.5625 2.5625 Unqualified . . 9430 1985-12-31 . 10000 1986-01-08 3680 1 2.5625 2.5 2.5 Unqualified . . 9200 1985-12-31 9430 10000 1986-01-09 3680 1 2.5625 2.5 2.5 Unqualified . . 9200 1985-12-31 9200 10000 1986-01-10 3680 1 2.5625 2.5 2.5 Unqualified . . 9200 1985-12-31 9200 10000 1986-01-13 3680 1 2.625 2.5 2.625 Unqualified . . 9660 1985-12-31 9200 10000 1986-01-14 3680 1 2.75 2.5 2.75 Unqualified . . 10120 1985-12-31 9660 10000 1986-01-15 3680 1 2.875 2.5 2.875 Unqualified . . 10580 1985-12-31 10120 10000 1986-01-16 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 10580 10000 1986-01-17 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040 10000 1986-01-20 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040 10000 1986-01-21 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040 10000 1986-01-22 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040 10000 1986-01-23 3680 1 3.75 2.5 3.75 Unqualified . . 13800 1985-12-31 11040 10000 1986-01-24 3680 1 4.1875 2.5 4.1875 Unqualified . . 15410 1985-12-31 13800 10000 1986-01-27 3680 1 4.4375 2.5 4.4375 Unqualified . . 16330 1985-12-31 15410 10000 1986-01-28 3680 1 4.4375 2.5 4.4375 Unqualified . . 16330 1985-12-31 16330 10000 1986-01-29 3680 1 4.4375 2.5 4.3125 Unqualified . . 15870 1985-12-31 16330 10000 1986-01-30 3680 1 4.4375 2.5 4.4375 Unqualified . . 16330 1985-12-31 15870 10000 1986-01-31 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1985-12-31 16330 10000 1986-02-03 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 . 10000 1986-02-04 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 16100 10000 1986-02-05 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 16100 10000 1986-02-06 3680 1 4.4375 2.5 4.1875 Unqualified . . 15410 1986-01-31 16100 10000 1986-02-07 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 15410 10000 1986-02-10 3680 1 4.4375 2.5 4.3125 Unqualified . . 15870 1986-01-31 16100 10000 1986-02-11 3680 1 4.4375 2.5 4.3125 Unqualified . . 15870 1986-01-31 15870 10000 1986-02-12 3680 1 4.4375 2.5 4.21875 Unqualified . . 15525 1986-01-31 15870 10000 1986-02-13 3680 1 4.4375 2.5 4.21875 Unqualified . . 15525 1986-01-31 15525 10000 1986-02-14 3680 1 4.4375 2.5 4.28125 Unqualified . . 15755 1986-01-31 15525 10000 1986-02-18 3680 1 4.4375 2.5 4 Unqualified . . 14720 1986-01-31 15755 10000 1986-02-19 3680 1 4.4375 2.5 3.9375 Unqualified . . 14490 1986-01-31 14720 10000 1986-02-20 3680 1 4.4375 2.5 3.6875 Unqualified . . 13570 1986-01-31 14490 10000 1986-02-21 3680 1 4.4375 2.5 3.6875 Unqualified . . 13570 1986-01-31 13570 10000 1986-02-24 3680 1 4.4375 2.5 3.625 Unqualified . . 13340 1986-01-31 13570 10000 1986-02-25 3680 1 4.4375 2.5 3.5625 Unqualified . . 13110 1986-01-31 13340 10000 1986-02-26 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-01-31 13110 10000 1986-02-27 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-01-31 11960 10000 1986-02-28 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-01-31 11960 10000 1986-03-03 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-02-28 16100 10000 1986-03-04 3680 1 4.4375 2.5 3.4375 Unqualified . . 12650 1986-02-28 11960 https://drive.google.com/file/d/1grx_6p-yVMVzrzvVjdtfwyFUjRpq10W6/view?usp=sharing

cleokatt

Hi I'm trying to calculate outliners within a datset trying: proc sql; SELECT CASE WHEN a.row_num <= ceil(0.25 * b.total_rows) THEN 1 WHEN a.row_num <= ceil(0.5 * b.total_rows) THEN 2 WHEN a.row_num <= ceil(0.75 * b.total_rows) THEN 3 ELSE 4 END AS quartile, AVG(a.value) AS quartile_value FROM (SELECT value, row_num() AS row_num FROM MYDATASET) AS a INNER JOIN (SELECT count(*) AS total_rows FROM MYDATASET) AS b ON 1=1 GROUP BY quartile; quit; My dataset have three col=sector (char), country (char), value (num) ERROR: Function ROW_NUM could not be located. ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: Expression using less than or equal (<=) has components that are of different data types. ERROR: Expression using less than or equal (<=) has components that are of different data types. 1451 quit; I can't use OVER or HAVING or such beacuse of my version of my computer,

PaigeMiller

I am trying to perform SG Annotation with BY Variables. In this trivial example, I want one plot with a red arrow for the plot of Male data; and one plot for the females with a green arrow, depending on the value of SEX in SASHELP.CLASS Here's my code that doesn't work (stealing from the example here). %sganno data sgannodata; set sashelp.class(obs=2); if sex='M' then do; %sgarrow(x1=51, x2=30, y1=30, y2=30, linecolor="red") end; else if sex='F' then do; %sgarrow(x1=51, x2=30, y1=30, y2=35, linecolor="green") end; keep sex; run; proc sort data=sashelp.class out=class; by descending sex ; run; proc sgplot data=class sganno=sgannodata; scatter x=age y=weight; by descending sex; run; Here's the incorrect output (the correct output would be the red arrow on the males and the green arrow on the females, not both arrows on both plots)

Puspita_1

Proc export giving additional quotes to the quoted char values.and if u use data step for that it is giving a space or . To numeric values which is not required . Needs to change nothing.now it is , , but want ,, no space no .for missing numeric value.

AshokD

Hi, I've a sample input data below and the expected output format. I need to find number of active customers as per the start date of the year and their based on end date, I've to keep the count added for each year. Example, Customer A,H,I - Active from 2004, so we have 3 customers active from 2004 and I'm keeping the count added horizontally for each year and by 2009 one customer got dropped. So for 2010 - customer count was 2. Similarly I've to populate the same for the next row active from 2005 and followed by other rows. Could you please suggest me the best approach to get this done ? Thanks in advance for your help.