Hi I would like to replace the following code with a prx function possibly prxmatch. Essentially I want to exclude any rows from a data set where a variable value has at least one bracket.   data work.&ds.; set work.&ds.; if index(&var.,"{")=0 and index(&var.,"}")=0 and index(&var.,"[")=0 and index(&var.,"]")=0 and index(&var.,"(")=0 and index(&var.,")")=0; run; ... View more

I have a task.   Import all tabs of an excel workbook (xlsx) into SAS. Tabs can be called anything and number of tabs is variable. I then need to read out all data sets to recreate the workbook as before, with tabs in their original order.   I have tried reading in excel files using libname statement with xlsx engine which reads them in ok, but does not enable the sheet order to be obtained. I tried using: proc sql & dictionary.columns sashelp.vtable proc contents but none of these methods help.     ... View more

Hi, I am creating a heat map in excel based on the "have" data set.   I would like to shade the "run" cells for each trial red if there is no "run" value for a trial. And shade cells green if there is at least one value for "run" within each trial.   So based on the data below, "trial" 002 would have all "run" values shaded red in excel. All other trials would have green "run" values in excel.   Thanks.   %let outfile=; /*add excel path here*/ filename _xlsx_ "&outfile."; data have; input trial : $3. environment : $4. run $1.; datalines; 001 PROD Y 001 TEST 002 TEST 002 PROD 003 PROD Y 004 TEST Y 004 PROD Y ; /*ODS excel start*/ ods excel file=_xlsx_ style=statistical; ods excel options(frozen_headers="1" autofilter="all" flow="table" sheet_name="Blueprint"); proc report data=have nowd; columns trial environment run; define trial / order; define environment / display; define run / display; run; /*ODS excel end*/ ods excel close; ... View more

Hi I would like to remove brackets plus any text inside, for example:   have: VAR1 text(where=(upcase(num)="A")) text text (in=a) text (in=a) text2 (where=(upcase(strip(var1))))   want: VAR1 text text text text text2   thanks for your help ... View more

Hi all I have some values containing text which can be in single or double quote marks. I would like to remove the quote marks plus the text inside e.g.   have: VAR1 hello "dog" is my pet just text hi 'people' how are you   want: VAR1 hello is my pet just text hi how are you   thanks for your help   ... View more

Hi all, I am trying to summarise some visit data.   This is the data I have:   USUBJID  VISIT                   DATE            MODULE 101           SCREENING       2000-01-02 101           SCREENING       2000-01-02 101           UNSCHEDULED 2000-01-02   VS 101           UNSCHEDULED 2000-01-02   VS 101           UNSCHEDULED 2000-01-02   LB 101           UNSCHEDULED 2000-01-04   EG 102           SCREENING       2000-01-05 102           VISIT 1                2000-01-06 102           UNSCHEDULED 2000-01-06   VS 102           UNSCHEDULED 2000-01-07   LB 103           VISIT 1                2000-10-06 103           VISIT 1                2000-10-06   This is what I would like. I would like one row per USUBJID/VISIT/DATE. However MODULE needs to be concatenated with commas, only listing distinct values for that USUBJID/VISIT/DATE USUBJID     VISIT                     DATE             MODULE 101              SCREENING         2000-01-02 101              UNSCHEDULED   2000-01-02    VS, LB 101              UNSCHEDULED   2000-01-04    EG 102              SCREENING         2000-01-05 102              VISIT 1                  2000-01-06 102              UNSCHEDULED   2000-01-06     VS 102              UNSCHEDULED   2000-01-07     LB 103              VISIT 1                   2000-10-06   I would appreciate any help.   Thanks! ... View more