Home

Quentin

Wondering about methods to hide a row in PROC TABULATE. Suppose I have a simple table with DATE in rows, PASS in column and have a total row. So daily data for April like: data have ; do date='01Apr2024'd to '30Apr2024'd ; do id=1 to 5 ; pass=ranuni(9)<.3 ; output ; end ; end ; format date date9. ; run ; proc tabulate data=have; class date pass ; tables (date all),pass ; run ; I want to make that table, but show only the rows for Apr 24 -Apr 30, and the ALL row. But I want the ALL row to reflect all 30 days, even though the table only shows rows for 7 days. In my mind, I want the table as above, but just hide the rows for Apr 1 - Apr 23. My first thought is to extend my data to have data as is for April 24-30, and then output all the rows again with date set to some arbitrary value that I can use to make an "ALL" row. So something like: data want ; set have ; if date>='24Apr2024'd then output ; date='09Sep2099'd ; output ; run ; proc format ; value datef '09Sep2099'd='01Apr2024 - 30Apr2024' other=[date9.] ; run ; proc tabulate data=want; class date pass ; tables date,pass ; format date datef. ; run ; That works and gives me the desired output: ---------------------------------------------------------- | | pass | | |-------------------------| | | 0 | 1 | | |------------+------------| | | N | N | |------------------------------+------------+------------| |date | | | |------------------------------| | | |24APR2024 | 3.00| 2.00| |------------------------------+------------+------------| |25APR2024 | 3.00| 2.00| |------------------------------+------------+------------| |26APR2024 | 4.00| 1.00| |------------------------------+------------+------------| |27APR2024 | 3.00| 2.00| |------------------------------+------------+------------| |28APR2024 | 5.00| .| |------------------------------+------------+------------| |29APR2024 | 4.00| 1.00| |------------------------------+------------+------------| |30APR2024 | 4.00| 1.00| |------------------------------+------------+------------| |01Apr2024 - 30Apr2024 | 113.00| 37.00| ---------------------------------------------------------- But I'm curious about other options. It almost feels like I could do this with a MULTILABEL format and a CLASSDATA dataset, but in the end I don't think that will work. I will be using ODS for the output (probably ODS POWERPOINT). So I guess I could try setting a STYLE attribute like ROWHEIGHT=0 for some rows (using CLASSLEV), and see if TABULATE will let me 'hide' a row that way.

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.

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)