How to check a variable type if date or Char after importing the Excel file in Import step

[waliaa]

Hi, my SAS program reads an excel file from a shared directory. There is a 'date' type field in the excel file which SAS sometimes reads as Date when it finds data in the first 8 rows (I reckon) and if there is no data in the first 8 rows then it automatically assigns char datatype(DT) to it. My program was working fine for the Char DT until SAS identified it as 'Date' for one of the excel files. 

Could someone please advise how I can write SAS code to  :

- first identify the data type in the SAS code (has to be done through code as it will part of an automated job), and

- if it's Date, run an extra step to convert it to Char and then continue with rest of the program

- if it's already char then do nothing

Alternatively, you may advise how to convert it to Date (if it has been identified as char) so I can then revise the rest of the code to align it with the same data type 

Any help will be greatly appreciated as I'm a new SAS learner and have a deadline to make this code work. (by 24th Oct)

Thanks a lot in advance. 

This is my proc import step: 

proc import out = permlib.filename_&macro.
datafile = "&macro_dir.\&macro"
dbms = excelcs
replace;
server="&PCFilesServer";
port=&PCFilesPort;
run;

[Tom]

I would check if you could use the PCFILES libref engine instead of PROC IMPORT.  Because then you might be able to use the DBSASTYPE dataset option to tell SAS what type to make the variable.

If it does not work then it is not hard to check the TYPE of a variable in a dataset.  This is such a common problem that there are many user written SAS macros available.  Many are "function" style macros.  Such as this classic from the late great Tom Hoffman that is almost 24 years old :

  https://github.com/sasutils/macros/blob/master/varexist.sas

So assuming VARNAME is the name of the variable that should be a DATE value then the code might look like this.

proc import
  datafile = "&macro_dir.\&macro"
  dbms = excelcs
  out = permlib.filename_&macro. replace
;
  server="&PCFilesServer";
  port=&PCFilesPort;
run;

%if (C = %varexist(permlib.filename_&macro.,VARNAME,type)) %then %do;
data permlib.filename_&macro. ;
  set permlib.filename_&macro. (drop=VARNAME);
  format VARNAME date9. ;
run;
%end;

[waliaa]

Thanks very much for such a great response Tom - really appreciated!!

PCfiles libref statement didn't work unfortunately. 

I looked at the github link that you provided and tried to replicate it for the data type function but got stuck as I have not yet created macros in a function style. 

This is what I tried and its surely incorrect:

%macro varexist(ds,var,info) ;

%local dsid rc vartype;
%let dsid = %sysfunc(open(&ds));

%if (&dsid) %then %do;
%let vartype = %sysfunc(vartype(&dsid,&var));

%if (&vartype = C) %then

------(not sure)

%mend varexist;

If you could kindly give me a bit more direction on this, would be greatly appreciated. I can then call it in the program after the proc import step. 

My other ques would be around the best way to call it in a program - would it be just pasting your code from the %if statement after the proc import step. That has thrown an error saying %IF statement is not valid in open code. 

Thanks again for your time looking into this.

[Tom]

No need to re-type the macro definition.  Just copy it.  Here is the "raw" page on github that just has the code.

https://raw.githubusercontent.com/sasutils/macros/master/varexist.sas

You could just highlight the text and copy it and paste it into the program editor.

Or you could ask SAS to do it for you by running these two statements 

filename varexist url "https://raw.githubusercontent.com/sasutils/macros/master/varexist.sas";
%include varexist;

There is no need to modify the macro definition. Once you have it defined you can use %varexist() just as if it was a SAS macro function provided by SAS.  As in the example code I posted already that should do what you want.  

The only code change you need to make is the name of the variable that should be tested (and fixed).  Since you did not tell us the name I just used a place holder name of VARNAME in my code.

%if (C = %varexist(permlib.filename_&macro.,VARNAME,type)) %then %do;
data permlib.filename_&macro. ;
  set permlib.filename_&macro. (drop=VARNAME);
  format VARNAME date9. ;
run;
%end;

[waliaa]

Thanks Tom, I managed to copy the macro as is from the link.
But as I pointed out earlier, when I paste your code in my program (after the import step), it throws a few errors as :
ERROR: The %IF statement is not valid in open code.
ERROR: Variable PersonReadDate is uninitialized.
ERROR: The %END statement is not valid in open code.

FYI, PersonReadDate is the variable that I'm trying to convert from the imported dataset

This is what I put exactly in my code (after import):
%if (C = %varexist(permlib.filename_&macro.,PersonReadDate,type)) %then %do;
data permlib.filename_&macro. ;
set permlib.filename_&macro. (drop=PersonReadDate);
format PersonReadDate date9. ;
run;
%end;

[Tom]

---

@waliaa wrote:

Thanks Tom, I managed to copy the macro as is from the link.
But as I pointed out earlier, when I paste your code in my program (after the import step), it throws a few errors as :
ERROR: The %IF statement is not valid in open code.
ERROR: Variable PersonReadDate is uninitialized.
ERROR: The %END statement is not valid in open code.

FYI, PersonReadDate is the variable that I'm trying to convert from the imported dataset

This is what I put exactly in my code (after import):
%if (C = %varexist(permlib.filename_&macro.,PersonReadDate,type)) %then %do;
data permlib.filename_&macro. ;
set permlib.filename_&macro. (drop=PersonReadDate);
format PersonReadDate date9. ;
run;
%end;

---

You must be running a very old version of SAS.  Remember that you are paying SAS an annual license fee.  So it is the same price to use the newest version as one that is 10 years old.

If you cannot use %IF in open code you could make a macro.

%macro fix_date(ds,var);
%if (C = %varexist(&ds,&var,type)) %then %do;
data &ds ;
  set &ds(drop=&var);
  format &var date9. ;
run;
%end;
%mend fix_date;

%fix_date(permlib.filename_&macro.,PersonReadDate);

Or use some other way to conditionally generate the code to fix the varaible.

[waliaa]

Thanks very much Tom; the new solution definitely helped but I had to tweak it a little to make it work at my end. Within the data step, I just added a couple of statements including an input fun as below (otherwise it was throwing some errors):

%macro fix_date(ds,var);
%if (C = %varexist(&ds,&var,type)) %then %do;
data &ds ; 

%local prd;

set &ds (rename=(&var = prd));  

  prd_date  = input(substr(prd, 1,10), ddmmyy10.);

  drop prd;

  format prd_date date9.;

  rename prd_date = PersonReadDate;

run;
%end;
%mend fix_date;

/* I could have just worked with &var rather than creating a new local var */

Good point on the SAS version - I might ask our tech team to install the latest available version on my laptop.

Greatly appreciate your help. Lots of learning from this particular issue:)

Cheers 

[Tom]

Your code is NOT using any macro variable name PRD that the %LOCAL statement is defining.  So you do not need to %LOCAL statement.

If you imported an EXCEL file and a variable that should have been a DATE variable has been created as a CHARACTER variable instead then you probable have a much bigger problem than you originally suggested.  Because that implies that some of the cells in the column have non-empty strings in them.  In that case the actual DATE values in that column will come over to SAS as the number of days since 1900 that EXCEL uses to store dates, only converted into a digit string.  So a column in Excel that "FRED"  in one cell and the date 01JAN2020 in the other will result in values in SAS of "FRED" and "43831".  So your attempt to fix it by using the DDMMYY informat is not going to work.

Instead you need to convert the string to a number using the normal numeric informat and then and the SAS date 30DEC1899 to adjust the base to reflect differences in how EXCEL and SAS number dates.

 prd_date  = input(prd,32.) + '30DEC1899'd ;

If the string might also have other date style strings that Excel did not recognize then you might even need to use some testing of the string value to decide which conversion logic to use.  For example if some of the cells had strings that were in DDMMYY style and other had actual date values you might do:

prd_date  = input(prd,ddmmyy10.)
if missing(prd_date) and not missing(prd) then prd_date  = input(prd,32.) + '30DEC1899'd ;

[Kurt_Bremser]

Instead of fixing all the crap caused by using Excel files, save your data as a csv file and read that with a data step, without relying on the guessing of PROC IMPORT.

[waliaa]

Yeah it's such a pain - the reason I'm trying to make it work with excel is because our stakeholders save it as.xlsx in the shared drive. If we use csv format in code then someone will have to manually convert the input files to csv.

[Kurt_Bremser]

---

@waliaa wrote:

Yeah it's such a pain - the reason I'm trying to make it work with excel is because our stakeholders save it as.xlsx in the shared drive. If we use csv format in code then someone will have to manually convert the input files to csv.

---

Saving as csv takes just "Save as" instead of "Save". If people don't manage that, they're too incompetent for their job in the first place.

Excel files have no place in a professional BI environment.