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

I need help converting an XLSX file to a true CSV file (i.e., a CSV file that doesn't contain any Excel formatting or highlighting).  I don't have SAS/ACCESS to PC files.   I write a lot of programs for data management to detect dirty data.  When the report doesn't produce any results, we aren't sure if it is because the data is clean or if my programming is wrong.  When the report is blank, I export the associated SAS datasets and give them to the data manager to update.  I will read in the modified file and run that data through the program to see if the programming is correct.   My problem is that if I read in a straight Excel file, SAS likes to pick and choose what formats and data types to apply to variables, which in turn causes errors in my code or causes more programming to get the right types, formats, and lengths applied to the test data.  My workaround was to just create a new dataset by setting the original table and then manually changing the fields that were updated by the DM.  For example:   data new; set original.old; if subjid='101-001-005' then weight=400; if subjid='101-002-003' then sex='F'; run; However, there are times when the DM makes a LOT of changes to many datasets, and it is too time consuming to manually enter every single update.  In those cases, I prefer to read in the data.  Since I mentioned above that SAS likes to automatically assigns data types and formats, I have found that I can save the XLSX file as a CSV and then read in a CSV file by using the meta data from the original library to assign the exact same data type, formats, and lengths.   That was a long background, but here is the root of my problem.  I wrote some VBscript that reads all the XLSX files in a folder and converts them to CSV.  If I were to manually open an XLSX file, I can "save as" CSV.  This CSV file will loose any Excel formatting (like highlighting, Excel formats, etc.).  But my problem is that in my code, it doesn't seem to save as a true CSV file.  Instead, it keeps all the highlighting as though it is still an XLSX file with a CSV extension.  When I then read in this newly converted CSV file, it read in as jumbled characters.   Attached is the original XLSX file that was updated by a DM.  I tried attaching the CSV file and the jumbled SAS dataset, but I couldn't get them to attach.  I can try to email those individually if you think you can help.   Can someone help me find out how I can convert XLSX to a true CSV?   Here is the VB script that I am using.   %macro convert_files(default=,inext=,outext=); data _null_; file "'&default\temp.vbs'"; put "set xlapp = CreateObject(""Excel.Application"")"; put "set fso = CreateObject(""scripting.filesystemobject"")"; put "set myfolder = fso.GetFolder(""&default"")"; put "set myfiles = myfolder.Files"; put "xlapp.DisplayAlerts = False"; put " "; put "for each f in myfiles"; put " ExtName = fso.GetExtensionName(f)"; put " Filename= fso.GetBaseName(f)"; put " if ExtName=""&inext"" then"; put " set mybook = xlapp.Workbooks.Open(f.Path)"; put " xlapp.Visible = false"; put " mybook.SaveAs ""&default.\"" & Filename & "".&outext"", 51"; put " End If"; put " Next"; put " mybook.Close"; put " xlapp.DisplayAlerts = True"; put " xlapp.Quit"; put " Set xlapp = Nothing"; put " strScript = Wscript.ScriptFullName"; put " FSO.DeleteFile(strScript)"; run; x "cscript ""&default\temp.vbs"""; %mend convert_files; %convert_files(default=C:\Users,inext=xlsx,outext=csv);   ... View more

I have written a program (that gets copied from one study to another) that specifies a list of ID variables that will be used throughout several subprograms.  I also have a couple of binary variables that are created by searching a table for the existence of a specified variable.  If it exists, I want it included in the ID variable list; otherwise, it should be excluded.   STUDYPART_EXIST=0 if STUDYPART does not exist and 1 if it does. BATCH_EXIST=0 if BATCH does not exist and 1 if it does.   This is a section of code that checks all 4 possible combinations of a variable existing to see if it is properly storing the necessary variable in the macro variable IDVARS:     %macro idvars (studypart_exist=, batch_exist=); %if &stdypart_exist.=1 & &batch_exist.=1 %then %do; %let idvars=%str(SITECOUNTRY SITEMNEMONIC SUBJECTNUMBERSTR STDYPART BATCH AESPID AETERM AESTDATC AEENDATC); %end; %if &stdypart_exist.=1 & &batch_exist.=0 %then %do; %let idvars=%str(SITECOUNTRY SITEMNEMONIC SUBJECTNUMBERSTR STDYPART AESPID AETERM AESTDATC AEENDATC); %end; %if &stdypart_exist.=0 & &batch_exist.=1 %then %do; %let idvars=%str(SITECOUNTRY SITEMNEMONIC SUBJECTNUMBERSTR BATCH AESPID AETERM AESTDATC AEENDATC); %end; %if &stdypart_exist.=0 & &batch_exist.=1 %then %do; %let idvars=%str(SITECOUNTRY SITEMNEMONIC SUBJECTNUMBERSTR AESPID AETERM AESTDATC AEENDATC); %end; %put &idvars.; %mend idvars; %idvars (studypart_exist=0, batch_exist=0); %idvars (studypart_exist=0, batch_exist=1); %idvars (studypart_exist=1, batch_exist=0); %idvars (studypart_exist=1, batch_exist=1);       I am fine using my original code, but just out of curiosity (and in an attempt to continue learning something new) is there a way to shorten my code?  If I had to conditionally include a new variable, then my code includes 8 combinations.  Another one would mean 16, etc.  It would be nice to keep this simple and just have a single line per conditional variable if possible.   I decided to try to use IF statements, but those are considered text and not IF statements.  Is there a way to simplify what I'm already doing?     %macro idvars (studypart_exist=, batch_exist=); %let idvars=%str(SITECOUNTRY SITEMNEMONIC SUBJECTNUMBERSTR) %if &stdypart_exist.=1 %then %do; STDYPART %end; %if &batch_exist.=1 %then %do; BATCH %end; %str(AESPID AETERM AESTDATC AEENDATC); %put &idvars.; %mend idvars; %idvars (studypart_exist=0, batch_exist=0); %idvars (studypart_exist=0, batch_exist=1); %idvars (studypart_exist=1, batch_exist=0); %idvars (studypart_exist=1, batch_exist=1);   ... View more