So that is still translating the greater than or equal to symbol into the plus minus symbol. You probably do not want to do that.   If you are running a SAS session using WLATIN1 encoding and read a dataset that was created using the UTF-8 encoding then it would properly transcode the plus minus symbol, but it would have trouble with the actual character you seem to have, which is the greater than or equal symbol.  That is because the plus minus symbol exists in the set of 256 characters that WLATIN1 supports, but the greater than or equal symbol does not.   So to do your own transcoding you need to force SAS to not automatically transcode the strings in the UTF-8 file.  You can do that by setting the ENCODING= dataset option on the input dataset.  You could set it to the encoding your current session is using, or just set it to ANY instead.  Now the bytes in the files will by copied into the character variables without ANY changes.   You will then need generate your own code to transcode ALL of the characters that use multiple bytes in UTF-8. So if you want to transcode the plus minus symbol you could use transwrd(_newvar,'C281'x,'81'x) But for the greater than or equal symbol you will need to replace it with two symbols since in WLATIN1 there is not on character that looks like that. tranwrd(_newvar, 'E289A5'x, '>='); And remember that there are potentially many other UTF-8 characters that you are now responsible for transcoding.   So it would be much easier to just run SAS using UTF-8 encoding to deal with that dataset and then all of the characters in the file will be displayed properly without you having to do anything. ... View more

Hi: Since I don't know what your data look like, so I created 2 excel files (havenum, havechar- X is defined as num and char ), please see attached files. Methods:  1. One of the approach is to convert excel files to CSV files- select CSV UTF-8(Comma Delimited).       then use PRO IMPORT. (Method 1 in the SAS code)       You may need to find a way to convert xls to CSV by using VBA code for a batch conversion.   2. To use ALLCHAR for SAS 9.4M5 users (Method 2 in the SAS code) /*STEP1: Save the xls file to CSV file To convert Excel to CSV file without losing data, first, navigate to File and click on Save As. From the Save As type dropdown, select CSV UTF-8(Comma Delimited). Click on Save. This saves the CSV file without losing any characters*/ /*STEP2: proc import ,dbms=csv ,text file Note: there is a new option but only available for SAS9.4, https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p12uk352fte2h1n1efh4r44dmxmp.htm*/ /* METHOD 1 */ PROC IMPORT OUT= havechar DATAFILE="C:\havechar.csv" DBMS=CSV REPLACE; guessingrows=33333; GETNAMES=YES; run; PROC IMPORT OUT= havenum DATAFILE="C:\havenum.csv" DBMS=CSV REPLACE; guessingrows=3333; GETNAMES=YES; run; /* METHOD 2 */ %let EFI_ALLCHARS=YES; PROC IMPORT OUT= m2havechar DATAFILE="C:\havechar.xlsx" DBMS=xlsx; run; PROC IMPORT OUT= m2havenumm DATAFILE="C:\havenum.xlsx" DBMS=xlsx; run; %let EFI_ALLCHARS=no; Output:   Note: It seems that Method 2 successfully keep the original format for characters- see the difference between yellow vs blue cell.  Method 1 (Yellow), X are aligned right , but Method 2(Blue) keeps the blanks    Reference Post:  allchar option https://communities.sas.com/t5/SASware-Ballot-Ideas/PROC-IMPORT-all-fields-as-character/idi-p/278415#M2011 Add an ALLCHAR option to the PROC IMPORT statement -- to import all variables as character. This should apply to external text files and to Excel files (considering CSV files to be text files) SAS document: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p12uk352fte2h1n1efh4r44dmxmp.htm   best wish, purple ... View more

The COMPRESS() function call is still not doing anything.  If you do not provide any characters for compress() to remove then it only removes the blanks.  But there is no need to remove the blanks to test if the string is blank or not.  SAS string comparisons already ignore trailing blanks. ... View more

hi @ballardw @Rick_SAS @PaigeMiller :   I finally accomplished the mission.  thanks a ton to all of you. this is the BEST place to ask for help for job related questions. 👍 here is the whole process:  1. sas macro  DATA2DATASTEP.sas  to display data values  from current SAS datasets  reference:  https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ 2. Raw Data- so everyone can test and use 3. prog sgplot  reference: https://blogs.sas.com/content/iml/2022/02/16/plot-series-missing-values.html    1. plotting 2 Y var. over X=Time1 on same graph  2. connecting the missing values with dashed line  3. display different xaxis scales by ID 4. Result is attached at the bottom.  Thank you, purple   /******************************************************************************************************** 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/ *******************************************************************************************************/ OPTIONS nomprint nomlogic; LIBNAME F "C:\Figure\"; %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 **********************************************************************/ ods results off; ods listing close; ods rtf file = "C:\plothave2.rtf"; ods graphics / height=5in width=8in; /*re-adjust the graphic size*/ proc sgplot data=have noautolegend; series x=time1 y=y1 /lineattrs=GraphData1(pattern=ShortDashDot thickness=3 color=red); /*creating a dashed line of Y is missing*/ series x=time1 y=y1/break markers markerattrs=(symbol=circle ) lineattrs=GraphData1(pattern=solid thickness=2 color=VIGB) name="Y1"; series x=time1 y=y2 / y2axis lineattrs=GraphData1 (pattern=shortdash thickness=5 color=red); /*creating a dashed line of Y is missing and must use y2axis to prevent 2nd plotting line display*/ series x=time1 y=y2/break y2axis markers markerattrs=(symbol=star) lineattrs=GraphData1(pattern=solid thickness=2 color=green) name="Y2";/*y2axis-to display y2 scale*/ by id; /*The VALUESHINT option combined with a VALUES=() list means the actual values of the variable are considered and will only display ticks from the values list that are appropriate, more or less.*/ xaxis valueshint value =(0 to 160 by 2 ) display=(nolabel) label = "Time(Day)"; yaxis grid values = (5 to 100 by 10) label="y1"; y2axis values= (0 to 100 by 10) label="y2"; /*USE 1.Noautolegend to suppress the autolegend display. 2.Name= to show Which legend */ keylegend "Y1" "Y2" / position=top location=outside Title="Y values legend"; 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

@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

@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: I see a few problems with the code you posted that was based on my example using SASHELP.SHOES: -- font_weight is misspelled as font_wight several times -- it looks like you have Parentheses around the ESCAPECHAR style string, and it needs to be curly braces. The form of ODS ESCAPECHAR is like this: ods escapechar='^'; ^{style[attribute=value attr2=val2] varvalue } Notice the open and close curly brackets, not parens. The code I posted worked for me in ODS EXCEL. BTW, I'm not sure why you are turning ODS RESULTS OFF. That could have ill effect.   One cell in Excel cannot have 2 background colors. If Excel won't let a cell have 2 different background colors, SAS can't change how Excel works. Let's take SAS out of the picture. The text within an Excel cell can have 2 different FOREGROUND or FONT colors. That is what ODS ESCAPECHAR is doing in my example. But try this as an experiment. go into Excel. Type several lines into a cell like this:   If you highlight one of the lines and try to format cells, you will ONLY be presented in Excel with a choice to  change the font characteristics, not the background fill.   after changing the text font to be different colors, I get this: Now, if I highlight the cell and then choose Format Cells, I can change the background fill for the entire cell: (changed the font back to black so it would not look too garish).      SAS is out of the picture in the above example, this is purely Excel formatting. And Excel doesn't allow one cell to have 2 backgrounds.     @Tom is correct. the first argument to CALL DEFINE has to be a column name (or the special tokes _COL_ and _ROW_ or an absolute column number). You have this:   call define("compress(substr(dt,12,8))",'style','style = [background = yellow]'); call define("compress(substr(bdt,12,8))", 'style','style = [background = yellow]'); That is not going to work for 2 reasons:  1) from the SAS/PROC REPORT end of things, you can't use a variable value from SUBSTR as the first argument to CALL DEFINE  2) from the Excel side of things, you can't have 2 different backgrounds in one cell in Excel     I guess I don't understand the purpose of having 2 different background colors in one cell.   But, with both SAS and Excel 2 different variable values in 2 different cells could have 2 different backgrounds:   and I can change the backgrounds (for an entire cell) conditionally like this:    Hope this helps. Cynthia ... View more

As @Tom pointed out. either the fold you stored xpt file does not exist ,or you don't have permission to enter it. ... View more

hi @Cynthia_sas and @Tom :  Now I fully understand the sandwich concept. have a great weekend. 😊   Here is the code and I tested.  /*Layer O*/ title; ods results off; ods listing close; ods escapechar="^"; ods excel file="C:\showABONLY.xlsx"; data A; set sashelp.class; keep name; run; data B; set sashelp.shoes; keep region; run; data C; set sashelp.cars; keep make; run; /*Layer inside*/ %macro tt(ds); %if &ds=A %then %do; ods excel options(autofilter="ALL" sheet_name="A"); proc report data=&ds; column name; define name/display; run; %end; ods excel options(autofilter="ALL" sheet_name="B"); %if &ds=B %then %do; proc report data=&ds; column region; define region/display; run; %end; %mend; %tt (A); %tt(B); /*Layer O*/ ods excel close; ods results on; ods listing; /*-----Dataset C- to suppress the output----*/ %tt(C);   ... View more

The first SET statement just reads the values and finds the first relevant observation for this subgroup (the _start pointer). The second set statement is called when there is an 'oral' value and the maximum is not too high, and it reads and outputs the observations from the start of the subgroup up to and including the current observation (automatic variable _N_).    After an 'oral' observation (and. if relevant, output of the subgroup), the _max_value is set to 0 because we are starting a new subgroup, and the current calculated maximum is not part of the new subgroup. Also, the observation pointer _start is set to the next observation to be read, in case we do not get a new ID in the next observation. What happens after the DO loop is really the same as what happens at the top (when encountering a new ID), but it has to be done in both situations, unless we are absolutely sure that each ID group ends with an 'oral' observation.   If we are certain that the last observation in an ID group is an 'oral', the code can be shortened to this: data want; set have; retain _max_value 0 _start 1; _max_value=max(value,_max_value); if route='oral'; if _max_value<=40 then do _p=_start to _N_; set have point=_p; output; end; _start=_N_+1; _max_value=0; retain _:; drop _:; run as we do not need to worry about the ID changing without an 'oral' observation first.   I hope this explains the logic behind my quite terse (but efficient) code.   ... View more

@Tom and all: I took all above suggestions and figured out.  thanks all ,this is a great learning and sharing place. purple ... View more

Yes, can always export to Excel or print it and include banding or formatting in that output. You can control that with various styles. https://support.sas.com/kb/23/647.html ... View more