I am writing a program for some clients that reads in a couple of excel files.  I give them the option to read in xls or xlsx because some of the files they are downloading are still using an old process and only provide xls files.  When I try to import the files, it converts all the time values to 01MAR2019 regardless of the time and saves it in a date9. format.  Is there a way to get SAS to recognize the column as a time field and treat it accordingly?  Is there an option or something in my import block that is missing?   PROC IMPORT OUT= WORK.FILE1 DATAFILE= "&dir.\&file1..xls" DBMS=EXCELCS REPLACE; RANGE="Page1_1$"; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; ... View more

This is just a quick question out of curiosity.  Please don't spend a lot of time trying to find a solution if you don't have one handy.   If possible, I would like to find any variables in a dataset that may contain leading zeroes.  The variable may be character or numeric.  For example, my clinical trials data might capture Site ID as a 3-digit value.  The first site would be entered as "001" as a character string.  I would like to loop through a dataset and find any such variables.  Is there a straightforward method for doing this?   My current thoughts are to loop through each variable.  If it is character string, I would check to see if there are any zeroes at the beginning of the string (no other digits between a "0" and a "." so "0.5" would not be considered a leading zero).  If it is numeric, I would convert it to a character string and then check like I would in the first step.  I would then flag any values that seem to fit this format.  I would then keep any variables that have at least one flag.  Does this sound like overkill?  It seems quite tedious and ineffective to me.  Is there a simpler way to accomplish this? ... View more

I need help finding a way to drop any variables that contain all missing values.  I have attempted to make a macro, but it seems to be getting a bit cumbersome, and I don't know how to finish what I've started.  Can someone help me simplify (or even complete) my code?   In my code below, I have a table TEMP that contains 5 variables.  They can be a combination of numeric or character.  My macro is looking at the input dataset and determining the number of variables in the table.  I then create a table MISSING that creates a corresponding numeric variable for each existing variable in TEMP and store in it the binary 0/1 from the missing() function.  I then use PROC MEANS to get the sum of each new variable.  In the output table MISSING_SUM, I get the system variable _FREQ_ and the sum of each created binary variable.  I figured that if _FREQ_=VARn, then I want to keep that variable and trace it back to the original data.   Is there a better thought process here, or is there a way I can finish my macro?  In the end, if I were to use PROC PRINT on the final result, it would display TEMP showing F1-F4 instead of F1-F5 since F5 is completely blank or missing.   data temp; input F1 $ F2 F3 F4 F5; cards; A 1 2 . . B 3 4 . . C 5 6 . . D . 5 1 . E 4 6 5 . F 4 . 6 . G 3 3 6 . H 5 3 2 . ; run; %macro missing_var (dsin=); proc sql noprint; select nvar into :nvar from sashelp.vtable where libname='WORK' & memname="&dsin."; %let nvar=%sysfunc(compress(&nvar.)); select name into :var1-:var&nvar. from sashelp.vcolumn where libname='WORK' & memname="&dsin."; quit; data missing (keep=var1-var&nvar.); set &dsin.; %do i=1 %to &nvar.; var&i.=missing(&&var&i.); %end; run; proc means data=missing noprint sum; var _all_; output out=missing_sum sum=; run; %mend missing_var; %missing_var (dsin=temp);   ... View more