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.
... View more
Two Identical Table one at work lib and another in sql sever - One getting generated thru SAS process @ Work Library and based on that I would like to simply update the SQL Server table. What is the best way to do so ?
I have 100+ columns in Work lib table and same in SQL server table.
I have library with SQL Server.
... View more
Hi guys,
how can I calculate person-years from two dates for ex: 16FEB2019 and 08JAN2020?
I have two variables with around 20.000 rows in my table.
Thank you in advance.
Best
... View more
Hi all, This one is quite frustrating. I want to return the Nth word of a string which matches (NOT case-sensitive) my 'search term'. Illustrated with example code using sashelp dataset. My code : *Create string which will be searched;
%let allowedTeams=montreal Cleveland ATLANTA houston Pittsburgh;
*Test dataset;
data work.baseballTest (drop=allowedT);
set sashelp.baseball(keep=team );
if _n_ < 20;
allowedT='montreal Cleveland ATLANTA houston Pittsburgh';
*Various styles of findw None of which appear to work;
findwRes1 = findw("&allowedTeams",team,' ','ei');
findwRes2= findw(allowedT,team,' ','e i');
findwRes3= findw("montreal Cleveland ATLANTA houston Pittsburgh",team,' ','e i');
findwRes4= findw(upcase("&allowedTeams"),upcase(team),' ','E');
findwRes5= findw("&allowedTeams",team,' ','i');
findwRes6= findw("&allowedTeams",team);
*Indexw locates the string. But extra work is needed to get the ;
*number of the word in the string;
indexwRes= indexw(upcase("&allowedTeams"), upcase(team));
if indexwres > 0 then do;
indexwRes1= countw(substr("&allowedTeams", 1, indexwRes), ' ', 'i');
end;
run; On running this code I attempt several different ways to get the FINDW to work for me (findwRres1-6) . Looking in the resultant dataset none of them do as they are all =0. I am able to use a workaround using indexw and countw (as shown) which does work correctly but is not as slick as the FINDW solution would be. Can anyone suggest what is going wrong with my findw attempts?
... View more
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.
... View more