data want (keep=usubjid startdtm stopdtm); set have1; retain temp_end temp_start; if intermediatestoptime ne . and restarttime ne . then do; startdtm= catx('T', put(date, date11.), put(starttime, time8.)); stopdtm= catx('T', put(date, date11.), put(intermediatestoptime, time8.)); output; startdtm= catx('T', put(date, date11.), put(restarttime, time8.)); stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.)); output; end; if intermediatestoptime eq . and restarttime eq . then do; startdtm= catx('T', put(date, date11.), put(starttime, time8.)); stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.)); output; end; if intermediatestoptime eq . and restarttime ne . then do; temp_end=restarttime; startdtm= catx('T', put(date, date11.), put(starttime, time8.)); stopdtm= catx('T', put(date, date11.), put(temp_end, time8.)); output; startdtm= catx('T', put(date, date11.), put(starttime, time8.)); stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.)); output; end; if intermediatestoptime ne . and restarttime eq . then do; temp_start=intermediatestoptime; startdtm= catx('T', put(date, date11.), put(starttime, time8.)); stopdtm= catx('T', put(date, date11.), put(intermediatestoptime, time8.)); output; startdtm= catx('T', put(date, date11.), put(temp_start, time8.)); stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.)); output; end; run; proc print data=want; run; ... View more

Thank you @Ksharp  for taking the time to look into the documentation and applying $varying format in my example.! Very grateful to @data_null__ for the precious tips and guidance! Appreciate your help, guys! ... View more

Hi @Ksharp ,  Thank you for the suggestion. Interesting, VISIT is also a grouping variable that is handled inside another grouping variable GR. This eliminated the unnecessary extra row. Could we also keep the original justification of text in the report?  VISITs are centered, GR is left justified. I tried with ods escapechar style elements, it didn't help.  ods escapechar='~'; proc report data=have; column visit gr paramcd aval; define visit/ order noprint; define gr/ order noprint; define paramcd/ 'Item' display; define aval/'Value' display; compute before gr/style={just=c}; line @1 text $40.; length text $40; if visit='Screen' then text='Screening'; else if visit='Week4' then text= 'Week 4'; else if visit='Week8' then text='Week 8'; if gr=2 then text="~S={just=left} EQ-5D-5L"; endcomp; run; ... View more

Similar approach to the above, but using sql and macro: /*Creating a macro variable "LABELS" that contains variable names and attributed labels*/ proc sql noprint; create table have as select distinct mnemonic, text from sashelp.webmsg where length(text) eq 55; select catx('=', strip(mnemonic), quote(compress(text, '', 'ka'))) into:labels separated by ' ' from have; quit; proc transpose data=have out=have_wide; var text; id mnemonic; run; /*Applying labels to all variables using macro variable "LABELS"*/ data want; set have_wide; label &labels; run; proc print data=want label; label; run; ... View more

options dlcreatedir; libname xyz "/usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/PanelOsh"; libname xyz "/usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/PanelOsh/Raw_Data"; libname xyz clear; ... View more

Hi,    Try to add options again with sheet_interval='NOW' option. eg. ods excel file='test.xls' options( sheet_interval='NONE' sheet_name="class" doc='help'); proc report data=sashelp.class; where sex='F'; run; proc report data=sashelp.class; where sex='M'; run; ods excel options( sheet_interval='now' sheet_name="cars"); proc report data=sashelp.cars; where type='SUV'; run; ods excel options( sheet_interval='none'); proc report data=sashelp.cars; where type='Wagon'; run; ods excel close; ... View more

It's hard to me figure out what the issue is, but if the missing observation solves the problem then my guess is you are sub-setting the data with 'if last.variable' condition. I think you get the desired number of observations if you remove: if last.CONSUMERNO; ... View more

Hi,  Use PROC FORMAT before proc report, then apply created format in proc report to display  the missing value as 'unknown'. ... View more

Alternatively, we can use 'style' attributes in proc report directly.  eg. ods excel file='want.xls'; proc report data=sashelp.class; column age name sex; define name/'Name' display; define age/'Age' display; define sex/'Sex' display; compute name; if age > 14 then call define(_col_, "Style", "Style=[fontstyle=italic font_weight=bold]"); endcomp; run; ods excel close; result. ... View more

Does this work? proc sql; create table want as (select*, count(distinct id) as gr1 from have group by phone having gr1 gt 1) union corr (select*, count(distinct id) as gr2 from have group by email having gr2 gt 1) order by email, phone, id; quit; ... View more

We can proc transpose data by ID where flag30=1, this will pick up both PAA and POSTDDA. The only concern is flag60, if this is always equal to flag30, then we can hardcode it 1.  eg: proc sort data=have; by id case; run; proc transpose data=have out=wide(drop=_name_); where flag30=1; by id; var flag30; id case; run; data want; set wide; if paa=1 and PostDDA=1 then do; multi_form=1; call missing(paa, postdda); end; flag30=1; flag60=1; /*if always 1 when FLAG30=1*/; proc print;run; variable order slightly changes, but it could be fixed  in data step or in the proc step where it needed. Or it could me merged back from the HAVE dataset. Obs id PAA PostDDA multi_form flag30 flag60 1 00119990 . . 1 1 1 2 00154629 . 1 . 1 1 3 00158730 1 . . 1 1   ... View more

New_Var=compress(mixed_variable, '', 'p'); To remove any punctuation or special characters ... View more