topic Re: Transposing Excel data ON IMPORT in SAS Programming

Transposing Excel data ON IMPORT

RandoDando — Fri, 14 Aug 2020 13:31:31 GMT

I have an fairly large Excel file which has begins with about a dozen columns with identifying information, and dozens more after that which have dates as the header (e.g. 8/1/2020, 8/2/2020...etc). Doing an import of the file, even with the validvarname=any option, does not bring in the column headings with date values. If I could do that, my intention would be to transpose the data IN SAS after that to have all the identifiers, and 1 date column with the corresponding values for each date. It would drastically increase the number of records, but make it far easier for processing.

Is there any way I can perform this transposing IN THE IMPORT procedure? I would rather avoid doing any work in Excel; doing complex functions in Excel is very processor intensive and my work computer isn't up to the task. Would rather have the SAS application server (EG) do the heavy lifting. 🙂

Re: Transposing Excel data ON IMPORT

ballardw — Fri, 14 Aug 2020 14:43:04 GMT

Not in Proc Import.

The Import procedure is basically designed around "nice" data with columns of data of a similar type with and column labels to use as variable names.

Since you have data, a date value, instead of a column label proc import has problems handling it.

Anything with other than nice behavior is going to require post processing if using Proc Import.

You might be able to us the XLSX Libname to access the sheets and some data step code but not much difference.

Assuming that your data comes in with a variable name with the data you can use a data step to transpose the data but I'm not going to suggest code without a concrete example of actual data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Re: Transposing Excel data ON IMPORT

ghosh — Fri, 14 Aug 2020 17:31:28 GMT

Using simulated data for transposing wide format to long when column header consists of date in mm/dd/yyyy format

The screen images show first partial data and then the partial transposed file

libname xl xlsx '~/dat/exchangerate.xlsx';
data have;
  set xl.exchange;
  run;
proc transpose data=have out=long (rename=(_label_=date col1=rate) drop=_name_);
by Currency;
var _numeric_;
  run;

/* New section added to convert date (m/d/yyyy) from char to date field */ 
data want;
  set long;
    real_date=put(input(date,mmddyy10.),date9.);
run;

the exchange rate dataPartial results: transposed to long format

Re: Transposing Excel data ON IMPORT

Tom — Fri, 14 Aug 2020 16:48:48 GMT

If I copy the html table from that page into Excel and save it then your code works. But I think it is because the hyphens in the dates are not hyphens. When I edit the text of the date values and replace the dashes with hyphen so that Excel makes the cells into actual dates instead of strings then I get the Excel date number as both the name and the label of the SAS variable.

To fix that you need to convert the text of the date number back into a DATE.

libname xl xlsx 'c:\downloads\xlsx_date_header.xlsx' ;
data raw;
 set xl.sheet1;
run;
libname xl clear;

proc transpose data=raw out=tall ;
  by Currency ;
run;

data want ;
  set tall ;
  date = input(_label_,32.)+'30DEC1899'd ;
  format date yymmdd10.;
run;

proc print data=want (obs=20) width=min;
run;

Obs        Currency         _NAME_    _LABEL_     COL1        date

  1    Australian dollar    _44050     44050      0.959    2020-08-07
  2    Australian dollar    _44053     44053     0.9561    2020-08-10
  3    Australian dollar    _44054     44054      0.952    2020-08-11
  4    Australian dollar    _44055     44055     0.9492    2020-08-12
  5    Australian dollar    _44056     44056     0.9463    2020-08-13
  6    Brazilian real       _44050     44050     0.2469    2020-08-07
  7    Brazilian real       _44053     44053     0.2466    2020-08-10
  8    Brazilian real       _44054     44054     0.2455    2020-08-11
  9    Brazilian real       _44055     44055     0.2427    2020-08-12
 10    Brazilian real       _44056     44056     0.2458    2020-08-13
 11    Chinese renminbi     _44050     44050      0.192    2020-08-07
 12    Chinese renminbi     _44053     44053     0.1918    2020-08-10
 13    Chinese renminbi     _44054     44054     0.1913    2020-08-11
 14    Chinese renminbi     _44055     44055      0.191    2020-08-12
 15    Chinese renminbi     _44056     44056     0.1903    2020-08-13
 16    European euro        _44050     44050     1.5765    2020-08-07
 17    European euro        _44053     44053     1.5701    2020-08-10
 18    European euro        _44054     44054      1.564    2020-08-11
 19    European euro        _44055     44055     1.5626    2020-08-12
 20    European euro        _44056     44056     1.5628    2020-08-13

Re: Transposing Excel data ON IMPORT

RandoDando — Tue, 18 Aug 2020 13:07:56 GMT

This solution worked to perfection. I only had to remove the _name_ and _label_ columns since I really had no use for them in this case. I have used workbooks as libraries in the past so I don't know how I didn't think of it. My only issue with that is replacing the workbook (or updating it) after using it in SAS. Even after clearing the library, you have to close out SAS completely before Windows will allow you to modify/delete/replace the workbook. Small annoyance.

Re: Transposing Excel data ON IMPORT

Tom — Tue, 18 Aug 2020 13:21:47 GMT

Even after clearing the library, you have to close out SAS completely before Windows will allow you to modify/delete/replace the workbook. Small annoyance.

Are you saying you are having trouble getting Excel to re-use the file you read? First use the ACCESS=READONLY option on the LIBNAME statement to prevents SAS from accidentally modifying the file. Second make sure to clear the libref after the code is done using it to get the data.

Or are you saying you are having trouble getting SAS to access the file if it is open in Excel? That is a real problem with Windows/Excel files. You have to close the spreadsheet in Excel before SAS can open it, even when it just wants to open it in readonly mode.

Re: Transposing Excel data ON IMPORT

RandoDando — Tue, 18 Aug 2020 13:25:27 GMT

I am not having any issues with SAS reading the Excel file and running the program similar to that above. Issue is, once I am finished with it in SAS, and clear the libref, I am unable to delete the original Excel file on my computer (such as to replace it with a new one) unless I first close SAS. Windows still thinks SAS is using it.