I'm trying to read in some Excel data using the Import Procedure.  DBMS XLSX (rows 31-42) changes the date values to an incorrect SAS date value.    DBMS EXCELCS (rows 43-55) produces the correct values if I have a short range name, however, it bombs if I have long range name (rows 55-62).   31         /*DBMS XLSX reads in data incorrectly*/ 32         proc import out=F1 33         datafile="S:\HC\ANDERSONTD\PRIVATE\102446\MISC\DateFormat_Example.XLSX" 34         dbms=XLSX replace; 35         getnames=yes; 36         Range="Short Name$a1:a28"; 37         run;   NOTE: The import data set has 27 observations and 1 variables. NOTE: WORK.F1 data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time):       real time           0.05 seconds       cpu time            0.04 seconds         38         39         data _null_; 40              set F1; 41              put EndDate; 42         run;   None Given None Given None Given 2                                                          The SAS System                             12:47 Wednesday, June 27, 2018   None Given None Given None Given None Given None Given None Given None Given None Given None Given 42735 42735 42735 42735 42277 42735 42277 42277 42277 42277 42277 42277 42277 42277 42277 NOTE: There were 27 observations read from the data set WORK.F1. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       cpu time            0.00 seconds         43         44         /*DBMS EXCELCS reads in data correctly, but has a short name*/ 45         proc import out=F2 46         datafile="S:\HC\ANDERSONTD\PRIVATE\102446\MISC\DateFormat_Example.XLSX" 47         dbms=EXCELCS replace; 48         Range="Short Name$a1:a28"; 49         run;   NOTE: PROCEDURE IMPORT used (Total process time):       real time           1.34 seconds       cpu time            0.23 seconds       NOTE: WORK.F2 data set was successfully created. NOTE: The data set WORK.F2 has 27 observations and 1 variables.   50         51         data _null_; 52              set F2; 53              put EndDate; 54         run;   None Given None Given None Given None Given None Given None Given None Given 3                                                          The SAS System                             12:47 Wednesday, June 27, 2018   None Given None Given None Given None Given None Given 12/31/2016 12/31/2016 12/31/2016 12/31/2016 9/30/2015 12/31/2016 9/30/2015 9/30/2015 9/30/2015 9/30/2015 9/30/2015 9/30/2015 9/30/2015 9/30/2015 9/30/2015 NOTE: There were 27 observations read from the data set WORK.F2. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       cpu time            0.00 seconds         55         56         /*However DBMS EXCELCS does not handle long range names (which I have)*/ 57         proc import out=F3 58         datafile="S:\HC\ANDERSONTD\PRIVATE\102446\MISC\DateFormat_Example.XLSX" 59         dbms=EXCELCS replace; 60         Range="This Worksheet Has A Long Name$a1:a28"; 61         run;   1                                                          The SAS System                             12:49 Wednesday, June 27, 2018   ERROR 65-58: Name 'This Worksheet Has A Long Name$a1:a28' is too long for a SAS name in this context. NOTE: PROCEDURE IMPORT used (Total process time):       real time           1.28 seconds       cpu time            0.21 seconds       ERROR: Import unsuccessful.  See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. 62         ... View more

In row 42 below I'm trying to select a numeric format name that I stored in a macro variable, but SAS seems to read it as character (see "NOTE 484-185" below).   What can be done to tell SAS it is numeric?   27 /*Store variable names*/ 28 %let varvals=Partictype RecPmtRcvd; 29 /*Store their associated format names*/ 30 %let formval=partictype yesno; 31 options mprint; 32 data temp1(keep=CJRorig Var_name Var_value); 33 length var_name var_value $20; 34 set CJR_Combine; 35 if CJRorig=1 then do; 36 /*Create an observation for each variable*/ 37 %macro selval; 38 %do i=1 %to 2; 39 /*Assign the variable name to Var_name*/ 40 Var_name="%scan(&varvals,&i)"; 41 /*Assign its formatted value to Var_value*/ 42 Var_value=put(Var_name,%scan(&formval,&i).); 43 %end; 44 %mend; 45 %selval MPRINT(SELVAL): Var_name="Partictype"; NOTE: Line generated by the macro function "SCAN". 45 partictype __________ 484 MPRINT(SELVAL): Var_value=put(Var_name,partictype.); MPRINT(SELVAL): Var_name="RecPmtRcvd"; NOTE 484-185: Format $PARTICTYPE was not found or could not be loaded. NOTE: Line generated by the macro function "SCAN". 45 yesno _____ 2 The SAS System 09:50 Tuesday, June 12, 2018 484 MPRINT(SELVAL): Var_value=put(Var_name,yesno.); NOTE 484-185: Format $YESNO was not found or could not be loaded. 46 output; 47 end; 48 run; NOTE: There were 3316 observations read from the data set WORK.CJR_COMBINE. NOTE: The data set WORK.TEMP1 has 793 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.04 seconds ... View more

In the proc report example below, is there a way to hide or suppress the column where y=4 from the output?   data a; input x 1. y 1.; datalines;   14 24 33 22 ; run; proc report data=a; columns x y; define x / group; define y / across; define n / format=8.; run;   ... View more

If I'm reading in an Excel worksheet using the libname xlsx option and the variable names are on 3rd row, for example, is there a way to tell SAS to use that row rather than the first row? ... View more

I apply the anyalpha function as a filter in row 35, however, it fails to stop all the cases where there is an alphabetic character.   However, when I attempt to flag them after creating a new variable it does pick them up (see row 51).   What gives?     28 data POS17; 29 length CCN $6; 30 set SAS.POS_DEC17(keep=&POSVARS); 31 /*AP filters, see item A.1.a.*/ 32 if PRVDR_CTGRY_CD = '01' 33 and PRVDR_CTGRY_SBTYP_CD = '01' 34 and length(PRVDR_NUM) = 6 35 and anyalpha(PRVDR_NUM) = 0 36 and ELGBLTY_SW EQ "Y" 37 and ORGNL_PRTCPTN_DT le "2017.0101" 38 and TRMNTN_EXPRTN_DT = '' or TRMNTN_EXPRTN_DT gt "2017.0101" 39 ; 40 if STATE_CD in ('AS', 'FN', 'GU', 'MP', 'MX', 'PR', 'VI', 'MD') 41 or PRVDR_NUM in ('050204' '100006' '310008' '330059' 42 '050238' '100073' '310012' '330119' 43 '050351' '100080' '310019' '330167' 44 '050481' '100131' '310054' '330198' 45 '050485' '100228' '310074' '330205' 46 '050603' '100253' '330024' '330214' 47 '050678' '100275' '330045' '360027' 48 '070022' '310005' '330046' '450056') 49 then delete; 50 ccn=trim(Prvdr_num); 51 if _n_ lt 10000 and anyalpha(CCN) gt 0 then put _n_= PRVDR_NUM=; 52 run; _N_=4259 PRVDR_NUM=03C0001312 _N_=4370 PRVDR_NUM=03X0009825 _N_=6220 PRVDR_NUM=04A327 ... View more

If I am filtering a variable with numbers with a "Best12." format, does it matter whether I enclose the filter value in quotes?   In other words,   If [varname] = '20'   or   If [varname]=20     ... View more