Yes. I realized that. I will keep as this unless the data is updated and got some records. thank you again. ... View more

hi all @PaigeMiller @Rick_SAS @ballardw : I updated the code and tested again, it perfectly worked. EXCEPT  1. log issue- how to work around the log issue :"WARNING: Y=Y2 is invalid. The option expects that the column not contain all missing values."   2. the Legend is not displaying.  3. since different ID had different  X Axis, how may I lable the minor ticker ?  thanks again. this is a really helpful place to solve questions.  Purple   /********************************************************************* PLOTTING -- 1. Need to show x scale by ID, do has to use dynamic Time1 2. Need to use SOLID LINES to connect data, but if there is missing Y, then connect with DASHED LINE **********************************************************************/ /*Plotting on same graph by ID*/ ods results off; ods listing close; ods rtf file = "C:\temp\plothave.rtf"; ods graphics / height=5in width=8in; proc sgplot data=have noautolegend; series x=time1 y=y1 /lineattrs=GraphData1(pattern=dash); /*creating a dashed line of Y is missing*/ series x=time1 y=y1/break markers lineattrs=GraphData1(pattern=solid thickness=2); series x=time1 y=y2/break markers y2axis ; by id; xaxis display=(nolabel) VALUESHINT label = "Time(Day)"; yaxis grid values = (5 to 100 by 10) label="y1"; y2axis values= (0 to 100 by 10) label="y2"; keylegend "y1 y2" / location=inside position=top; /*where does this legend go? */ run; ods rtf close; ods listing; ods results on;   ... View more

Hi Geeks:  I spent some time to use the @PaigeMiller to get "https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/"  1. the sasdata , now it is available.  2. proc means does show Y2 for  ID=884-008 has ALL missing values (but that is what the data is) 3. Run sgplot (or maybe other plotting procedures)   Goal:  1. **Need to show x scale by ID, how to create dynamic Time1 scale-X Axis? 2.** Need to use SOLID LINES to connect data, but if there is missing Y, then connect with DASHED LINE   Thanks all for your input. 🙏   /******************************************************************************************************** Porgram name: DATA2DATASTEP.sas Goal: Turn a SAS DATA set into a DATA step Reference: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ *******************************************************************************************************/ LIBNAME F "C:\temp"; %macro DATA2DATASTEP(dsn,lib,obs); /*Step1: SQL query that gets a space delimited list of the variable names*/ proc sql noprint; select Name into :varlist separated by ' ' from dictionary.columns where libname="&lib" and memname="&dsn"; quit; %put &varlist; /*Step2: Gets a space delimited list of the variable names concatenated with a ':' and required informat, which we will use on the DATA step INPUT statement to read the values in from the DATALINES:*/ proc sql noprint; select case type when 'num' then case when missing(format) then cats(Name,':32.') else cats(Name,':',format) end else cats(Name,':$',length,'.') end into :inputlist separated by ' ' from dictionary.columns where libname="&lib" and memname="&dsn"; quit; %put &inputlist; /*Step3: DATA _NULL_ step to write a DATA step so we can copy and paste data to the Section: RAW DATA */ data _null_; set &lib..&dsn(obs=&obs) end=last; put &varlist @; if last then do; put; put ';;;;'; end; else put; run; %mend; %data2datastep (HAVE,F,343) /*----------------------------------------------------------------------------------------- RAW DATA ------------------------------------------------------------------------------------------*/ data have; infile datalines dlm='' missover /*end missing*/ dsd /*beginning and middle*/; input ID $ TIME1 y1 y2; datalines; 818-001 1 62 818-001 2 74 818-001 3 79 7.7 818-001 4 73 7.7 818-001 5 7.7 818-001 6 67 8.8 818-001 7 70 10.5 818-001 8 68 12.3 818-001 9 67 14 818-001 10 63 14.5 818-001 11 62 14.5 818-001 12 63 15.5 818-001 13 69 15.5 818-001 14 66 15.5 818-001 15 66 15.5 818-001 16 16.6 818-001 17 64 17.5 818-001 18 69 18.3 818-001 19 64 19.2 818-001 20 73 19.2 818-001 21 66 20 818-001 22 74 20 818-001 23 62 21 818-001 24 58 21 818-001 25 71 22.9 818-001 26 62 24.6 818-001 27 58 24.6 818-001 28 69 24.6 818-001 29 65 26.3 818-001 30 60 26.3 818-001 31 68 28 818-001 32 63 28 818-001 33 70 28 818-001 34 69 28 818-001 35 66 28 818-001 36 80 29.8 818-001 37 66 29.8 818-001 38 66 31.5 818-001 39 67 31.5 818-001 40 66 31.5 818-001 41 61 33.2 818-001 42 70 33.2 818-001 43 61 35 818-001 44 66 35 818-001 45 60 35 818-001 46 62 35 818-001 47 61 36.7 818-001 48 61 36.7 818-001 49 61 36.7 818-001 50 36.7 818-001 51 63 38.4 818-001 52 38.4 818-001 53 69 40.1 818-001 54 60 40.1 818-001 55 59 40.1 818-001 56 62 41.9 818-001 57 60 41.9 818-001 58 60 41.9 818-001 59 64 41.9 818-001 60 41.9 818-001 61 53 41.9 818-001 62 57 44.1 818-001 63 59 818-001 65 61 818-001 66 61 818-001 67 61 818-001 68 60 818-001 69 62 818-001 70 59 818-001 71 62 818-001 72 60 818-001 73 59 818-001 74 64 818-001 75 71 818-001 76 63 818-001 77 60 818-001 78 64 818-001 79 61 818-001 82 56 818-001 84 61 818-001 93 57 818-001 94 55 848-001 1 55 848-001 2 52 2 848-001 3 6 848-001 4 8 848-001 5 8 848-001 6 10 848-001 7 43 10 848-001 8 42 10 848-001 9 41 10 848-001 10 42 12 848-001 11 51 12 848-001 12 43 14 848-001 13 40 14 848-001 14 44 16 848-001 15 49 16 848-001 16 46 18 848-001 17 46 848-001 18 49 848-001 19 60 848-001 20 47 22 848-001 21 49 848-001 22 53 848-001 23 50 848-001 24 54 848-001 26 54 848-001 27 52 851-001 1 56 851-001 2 44 2 851-001 3 6 851-001 4 8 851-001 5 36 851-001 6 42 851-001 7 41 851-001 8 52 851-001 9 46 9 851-001 10 41 9 851-001 11 56 10 851-001 12 56 10 851-001 13 46 9 851-001 14 60 9 851-001 15 10 851-001 16 45 10 851-001 17 42 10 851-001 18 51 10 851-001 19 58 11 851-001 20 48 11 851-001 21 47 11 851-001 22 11 851-001 23 46 12 851-001 24 12 851-001 25 12 851-001 26 12 851-001 27 52 13 851-001 28 61 13 851-001 29 54 13 851-001 30 13 851-001 31 14 851-001 32 51 14 851-001 33 53 14 851-001 34 46 14 851-001 35 15 851-001 36 15 851-001 37 52 15 851-001 38 15 851-001 39 16 851-001 40 16 851-001 41 40 16 851-001 42 61 16 851-001 43 57 17 851-001 44 56 17 851-001 45 53 17 851-001 46 17 851-001 47 53 18 851-001 48 57 18 851-001 49 54 18 851-001 50 54 18 851-001 51 42 20 851-001 52 52 20 851-001 53 56 20 851-001 54 20 851-001 55 55 22 851-001 56 52 22 851-001 57 53 22 851-001 58 22 851-001 59 24 851-001 60 57 24 851-001 61 46 24 851-001 62 46 24 851-001 63 24 851-001 64 46 26 851-001 65 26 851-001 66 26 851-001 67 53 26 851-001 68 28 851-001 69 46 28 851-001 70 51 28 851-001 71 48 28 851-001 72 50 30 851-001 73 53 30 851-001 74 49 30 851-001 75 49 30 851-001 76 46 32 851-001 77 32 851-001 78 37 32 851-001 79 46 32 851-001 80 43 34 851-001 81 34 851-001 82 40 34 851-001 83 45 34 851-001 84 41 36 851-001 85 36 851-001 86 42 36 851-001 87 36 851-001 88 45 38 851-001 89 38 851-001 90 41 38 851-001 91 36 38 851-001 92 42 40 851-001 93 50 40 851-001 94 40 851-001 95 45 40 851-001 96 40 851-001 97 40 851-001 98 44 40 851-001 99 60 40 851-001 100 40 851-001 101 49 40 851-001 102 42 851-001 103 55 42 851-001 104 40 42 851-001 105 46 42 851-001 106 44 851-001 107 40 44 851-001 108 43 44 851-001 109 44 851-001 110 40 46 851-001 111 38 46 851-001 112 46 851-001 113 40 46 851-001 114 42 48 851-001 115 48 851-001 116 43 48 851-001 117 48 851-001 118 48 50 851-001 119 42 50 851-001 120 50 851-001 121 36 50 851-001 122 38 52 851-001 123 52 851-001 124 31 52 851-001 125 40 52 851-001 126 35 54 851-001 127 37 54 851-001 128 44 54 851-001 129 54 851-001 130 52 56 851-001 136 56 851-001 132 43 56 851-001 133 56 851-001 134 40 58 851-001 135 58 851-001 136 46 58 851-001 137 58 851-001 138 48 60 851-001 139 49 60 851-001 140 41 60 851-001 141 36 60 851-001 142 47 62 851-001 143 46 62 851-001 144 44 62 851-001 145 48 62 851-001 146 40 64 851-001 147 35 64 851-001 148 46 64 851-001 149 47 64 851-001 150 66 851-001 151 48 66 851-001 152 38 66 851-001 153 42 66 851-001 154 68 851-001 155 35 68 851-001 156 68 851-001 157 68 851-001 158 70 851-001 159 40 851-001 160 38 851-001 161 37 851-001 162 34 851-001 164 41 851-001 165 35 851-008 1 35 851-008 2 41 2 851-008 3 37 6 851-008 4 35 8 851-008 5 37 8 851-008 6 37 9 851-008 7 37 9 851-008 8 37 10 851-008 9 36 10 851-008 10 34 11 851-008 11 37 11 851-008 12 38 12 851-008 13 35 12 851-008 14 37 12 851-008 15 33 12 851-008 16 33 13 851-008 17 33 13 851-008 18 34 14 851-008 19 33 14 851-008 20 33 15 851-008 21 33 15 851-008 22 33 16 851-008 23 36 16 851-008 24 35 16 851-008 25 33 16 851-008 26 32 16 851-008 27 33 16 851-008 28 35 17 851-008 29 37 17 851-008 30 32 17 851-008 31 33 17 851-008 32 31 18 851-008 33 36 851-008 35 32 851-008 36 31 851-008 37 31 851-008 38 33 851-008 39 39 851-008 40 35 851-008 41 38 884-008 1 53 884-008 3 50 884-008 6 56 884-008 7 56 884-008 8 59 884-008 9 51 884-008 10 55 884-008 11 56 884-008 12 58 884-008 13 60 884-008 14 62 884-008 15 65 884-008 16 64 884-008 17 59 884-008 18 60 884-008 19 57 884-008 20 54 884-008 21 61 884-008 22 60 884-008 23 57 884-008 24 57 884-008 25 59 884-008 26 62 884-008 27 63 884-008 28 60 884-008 29 64 884-008 30 63 884-008 31 64 884-008 32 57 884-008 33 51 884-008 34 54 ; run; proc sort;by id time1;run; /********************************************************************* CHECK MISSING DATA VALUES **********************************************************************/ proc means data=have min max N NMISS; var time1 y1 y2; BY ID; run; /********************************************************************* PLOTTING 1. **Need to show x scale by ID, how to create dynamic Time1 scale-X Axis? 2.** Need to use SOLID LINES to connect data, but if there is missing Y, then connect with DASHED LINE **********************************************************************/ /*Plotting on same graph by ID*/ ods results off; ods listing close; ods rtf file = "C:\temp\plothave.rtf"; proc sgplot data=have noautolegend; series x=time1 y=y1/break markers ; series x=time1 y=y2/break markers y2axis ; by id; xaxis display=(nolabel) label = "Time(Day)"; yaxis grid values = (5 to 100 by 10) label="y1"; y2axis values= (0 to 100 by 10) label="y2"; keylegend "y1 y2" / location=inside position=top; run; ods rtf close; ods listing; ods results on; ... View more

@Rick_SAS : Thank you for helping. Here is the proc means output and snippet of "Have" dataset - Y1, Y2 had some missing values , but NOT all were missing. - Time1 is numeric variables  -Data was taken from sas datasets     ... View more

Hi @PaigeMiller :   Have data is attached again as have.sas7bdat log is attached as havelog.docx   Thanks so much for helping.   here is code  data have; set f.have; run; proc sort;by id time1;run; /*Plotting on same graph by ID*/ ods results off; ods listing close; ods rtf file = "C:\Users\Temp\plot.rtf"; proc sgplot data=have noautolegend; series x=time1 y=y1/break markers ; series x=time1 y=y2/break markers y2axis ; by id; xaxis display=(nolabel) label = "Time(Day)"; yaxis grid values = (5 to 100 by 10) label="y1"; y2axis values= (0 to 100 by 10) label="y2"; keylegend "y1 y2" / location=inside position=top; run; ods rtf close; ods listing; ods results on;   ... View more

I added one step and got the Missing column. thanks all for this amazing forum.   data want2; set want; by name; /*lag function to carry forward END*/ retain a ; if first.name then call missing(a); if end ne . then a=end; else end=a; format a end date9.; /*compare END with alldates, any dates out of boundary are Missing dates*/ if alldates>end then missing=alldates; format missing date9.; keep name start end e alldates missing ; run;         ... View more

Hi @PaigeMiller : Thanks for quick response and advise. The output didn't show the column - Missing. I don't know how to get this column.            ... View more

@DanH_sas : The question is How to show Different x axis scale by subject.   eg. subject 101  max x axis =100 subject 102 max x axis=15 subject 103 max x axis=40   thanks for helping, purple ... View more

Hi Kurt: I mistakenly posted twice. thanks for reminder. ... View more

Hi all: I have this question: -How to display x axis max scale by subject.?  thank you, purpleclothlady /*axis1 order=(1 to 150 by 7) - max value of x axis will change by subject. eg 001 max is 150, 002 max is 60 , 003 max is 90*/ axis1 order=(1 to 150 by 7) label= (h=1.2 "Time(Day)" justify=center color=black) ; axis2 offset=(1,1) order=(0 to 100 by 10) label=( angle=90 h=1 color=blue ""result"); legend1 label=none value=(color=black height=1.2 "result") position=(bottom center outside); proc gplot data=rpt; plot _result*day / legend=legend1 haxis=axis1 vaxis=axis2 ; by subjid; run; quit; ... View more

typo: ods excel file="C\test.xlsx" should be ods excel file="C:\test.xlsx" ... View more

hi @Cynthia_sas : Thanks so much for the details of diagnosis. Example1:  here is the code. It worked before. I didn't say it clearly : I am asking if there is a way to highlight partial cell (NOT highlight partial of a text string)     ods excel options ; proc report data = rpt nowd split='*' missing style(header)=[just=center]; /*columns variable order to correctly display compute block*/ column subject dt bdt flagdt; define subject / "ID" order=data ; define dt / "Date/Time"  ; define bdt / "Date/Time"  ; define flagdt / display noprint; compute flagdt; if flagdt = "ti" then do; call define("compress(substr(dt,12,8))",'style','style = [background = yellow]'); call define("compress(substr(bdt,12,8))", 'style','style = [background = yellow]'); end; else if flagdt = "dt_ti" then do; call define("dt",'style','style = [background = purple]'); call define("bdt", 'style', 'style = [background = purple]'); end; endcomp; run;   Example2: escapchar =' ^'     I  tested the code, somehow it didn't work ,here is the code: please advise. thanks again.  Purple proc sort data=sashelp.shoes out=newshoes; where region contains "Europe" and sales >100000 and (product contains "Casual" or product contains "Dress"); by region product; run; data newshoes; set newshoes; product=strip(product); region=strip(region); run; /*output*/ title; footnote; ods results off; ods listing close; ods escapechar = '^'; ods excel file="C\test.xlsx" style=excel options (row_repeat='header' frozen_headers="ON" autofilter ="all" frozen_rowheaders="ON" ROWBREAKS_INTERVAL= 'OUTPUT' sheet_label=' ' embedded_titles='YES' FitToPage='ON' orientation="landscape" flow="ROWHEADERS" ); ods excel options (sheet_name="Testcolor") ; proc report data=newshoes nowd split='*' missing style(header)=[just=center];; title 'test'; column region showreg product showprod sales inventory returns; define region/display; define showreg/computed; define product/display; define showprod/computed; define inventory/display; define returns/display; compute showreg/character length=80; showreg=catx('',scan(region,1,' '),'^(style[color=orange font_weight=bold]',scan(region,2,' '),')'); endcomp; compute showprod/character length=80; if scan(product,2,' ') = 'Casual' then do; showprod=catx(' ', scan(product,1,' '),'^(style[color=purple font_wight=bold]',scan(product,2,' '),')'); end; if scan(product,2,' ') = 'Dress' then do; showprod=catx(' ', scan(product,1,' '),'^(style[color=green font_wight=bold]',scan(product,2,' '),')'); end; endcomp; run; ods excel close; ods results on; ods listing; ods output excel :     ... View more