Импортировать переменные с символьными и числовыми значениями от excel до SAS

Pari T спросил: 28 апреля 2018 в 09:25 в: excel

У меня есть набор данных в excel, переменные содержат значения как с символьным, так и с числовым форматами.

var1 -------- var2

352,45 ----- < 34,5

, когда я импортирую их в SAS, var2 пропадает, как я могу сохранить или приписать "< 34,5" для var2?

Я использую следующий код для импорта:

PROC IMPORT OUT= data
DATAFILE= "data1.xlsx" 
DBMS=EXCEL REPLACE;
RANGE="Sheet1$"; 
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

3 ответа

Richard ответил: 28 апреля 2018 в 03:06

Быстрый ответ

Измените запись реестра Windows с помощью RegEdit. В моей системе Windows 10, x64, Office 2016, запись

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRow

изменится на

0

Объяснение

Proc IMPORT DBMS=EXCEL выводит столбец var2 - это числовое содержимое, потому что он не проверял достаточно строк Excel, чтобы обнаружить, что существует некоторое числовое число. Таким образом, при этом выводе при достижении нечислового содержимого процедура заменяет его отсутствующим значением.

Вам придется

  1. принудительно импортировать достаточно строк, чтобы найти -numerics в столбце (если присутствует).
    • Заставляет IMPORT определять эти столбцы (переменные) как тип символа.
  2. Постобработать импортированный набор данных на этапе DATA
    • Выполняет преобразования, которые преобразуют числовые переменные ожидаемые в действительную числовую переменную.

Сканирование большего количества строк - Excel

Нет опции Proc IMPORT для изменения количества проверенных строк Excel.

Proc IMPORT DBMS=Excel в Windows использует технологию Microsoft для чтения файлов Excel. В версии SAS < 9.2 Phase 2 технология Jet и новые версии используют ACE. Каждая из этих технологий использует реестр Windows для получения параметра TypeGuessingRows, который представляет собой количество строк, которые нужно отсканировать, прежде чем выводить столбец - это символ, число или дата. Когда значение параметра 0, все строки проверяются перед выводом.

Документация SAS "SAS / ACCESS® 9.4 Интерфейс с файлами ПК: справочник, четвертое издание" в главе "Microsoft Excel "Файлы рабочих книг" содержит сведения о записи реестра Windows, которые необходимо будет изменить на основе установки системы и Office. Как упоминалось в в быстрой моей системе

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRow

По умолчанию строки 8 были изменены на 0.

Бонус: сканирование большего количества строк - файлы с разделителями

Это будут текстовые файлы с полями, разделенными запятыми, вкладками или другим символом.

Proc IMPORT DBMS=CSV может быть принудительно сканировать все строки с помощью инструкции GUESSINGROWS

Proc IMPORT --all my options-- out=import_raw;
  GUESSINGROWS=MAX; * statement;
run;

Шаг 2 - Почтовая обработка

Вот пример кода, показывающий, как известный "сложный" столбец преобразуется в один и тот же именованный числовой столбец. Правило заключается в том, что значение <#### будет преобразовано в ####.

data import;
  set import_raw (rename=var2=var2_raw);  if var2_raw =: '<' then 
    var2 = input(substr(var2_raw,2), best12.);
  else
    var2 = input(var2_raw,best12.);  drop var2_raw;
run;

Ваши фактические правила для преобразование может отличаться - например, вам может понадобиться добавить столбец $1 var2_relation, который принимает значения =, < или . Или вы можете сжать значение, удалив все символы, отличные от числа, перед выполнением преобразования ввода ().

Это был бы другой вопрос, если у вас возникли проблемы с кодированием решения, которое делает то же самое преобразование для всех переменных.

Pari T ответил: 28 апреля 2018 в 10:41
спасибо, но GUESSINGROWS не работает для файлов excel, но только для csv. Есть ли что-то, что может сделать ту же работу для Excel?
Richard ответил: 28 апреля 2018 в 10:47
В какой строке Excel var2 сначала показывает < ?
Pari T ответил: 28 апреля 2018 в 10:49
Он существует только в строках 27 и 30 файла Excel.
Richard ответил: 28 апреля 2018 в 11:09
Вам придется изменить настройки реестра Windows. См. Обновленный ответ
Tom ответил: 28 апреля 2018 в 01:11
Я думаю, что если вы используете движок XLSX вместо EXCEL-механизма, вам не нужно менять количество строк, которые он анализирует. Он должен проверить весь столбец.
Joe ответил: 29 апреля 2018 в 12:54

Как отмечали другие, скорее всего, механизм Excel не сканирует достаточно далеко, чтобы найти значения символов.

Двигатель XLSX, новый, я верю в 9.3 и, как правило, полностью функциональный как для импорта, так и для экспорта в 9.4, будет лучше сканировать работу для переменных типов. Он довольно прост в использовании, просто замените DBMS=EXCEL на DBMS=XLSX. Это не будет совершенно идентично, если у вас есть (например) GETNAMES=NO, но для большинства целей он идентичен.

PROC IMPORT OUT= data
DATAFILE= "data1.xlsx" 
DBMS=XLSX REPLACE;
SHEET="Sheet1"; 
GETNAMES=YES;
RUN;
Pari T ответил: 29 апреля 2018 в 08:57
В 9.4 это, похоже, не работает.
Joe ответил: 29 апреля 2018 в 09:12
Что не работает? СУБД = XLSX, безусловно, работает в 9.4. Не исправляет ли ваш персонаж / числовую проблему?
Pari T ответил: 29 апреля 2018 в 10:13
Это журнал, который я получаю с помощью XLSX: (Это не происходит для Excel) 3365 PROC IMPORT OUT = data1 3366 DATAFILE ="data.xlsx" 3367 DBMS = XLSX REPLACE; 3368 SHEET ="Sheet1 $"; 3369 GETNAMES = ДА; 3370 RUN; ОШИБКА: Не удалось найти лист в электронной таблице. ПРИМЕЧАНИЕ. Система SAS перестала обрабатывать этот шаг из-за ошибок. ПРИМЕЧАНИЕ: ИМПОРТ ПРОЦЕДУРЫ используется (общее время процесса): в реальном времени 0,04 секунды времени процессора 0,00 секунды
Tom ответил: 29 апреля 2018 в 11:46
Если вам нужен только первый лист, нет необходимости в выражении SHEET=. Вы уверены, что файл является фактическим файлом XLSX? Сделайте копию и измените расширение на ZIP и проверьте, можете ли вы видеть файлы XML внутри ZIP-файла.
Joe ответил: 29 апреля 2018 в 12:54
"$" Был неправильным, извините; Я удалил.
Wired604 ответил: 28 апреля 2018 в 11:20

У вас возникли проблемы с использованием infile вместо импорта proc? Также, если число и символы находятся в одном и том же столбце, вам нужно будет заставить его использовать символы.

http://www2.sas. com / examples / forum2008 / 166-2008.pdf

Ниже приведен код, который я получил из цикла, но вы должны понять это. Обратите внимание, что когда colomn следует за $ в инструкции infile, он заставляет colomn быть символами!

 filename file&i "&fdir";   /*THIS ASSIGN FILE NAME RELATED WITH THE DIRECTORY IN PREVIOUS FILE*/
      data &name; /*USE THE FULL PATH OF THE FILE NEEDED AS PER 1ST TABLE*/
LENGTH  BAN $10.;
    LENGTH  SUBSCRIBER_NO   $10.;
    LENGTH  TRANSACTION_DATE    $18.;
    LENGTH  OPT1    $18.;
        INFILE file&i delimiter = ',' MISSOVER DSD LRECL=32767 FIRSTOBS=2 flowover;
        input SUBSCRIBER_NO $   BAN $   OPT1 $  TRANSACTION_DATE $  TRANSACTION_TYPE $  ITEM_ID $   MSID $  NIN1 $  ACTIVATION_TYPE $   STORE_CODE $    OPT8 $  OPT10 $     OPT9 $  WES $   BILL_CYCLE $    LANGUAGE_CODE $     REGION $    COMPANY_CODE $  PRICE_PLAN $    COMMIT_START_DATE $     SYS_CREATION_DATE $     RENEWAL_DATE $  ESN_TYPE $  ACCOUNT_TYPE $  EFFECTIVE_DATE $    INIT_ACTIVATION_DATE $  TENURE $    DATA $  PRICE_PLAN_DATA $   OPT3 $  PRICE_PLAN_DESC $   MSF $   PRICE_PLAN_SERIES $     ACTIVATION_DATE $   OPT5 $  TERM_STATUS $   OPT4 $  FIRST_NAME $    LAST_BUSINESS_NAME $    ADDRESS_ATTENTION $     USER_NAME $     ADDRESS_NAME_1 $    ADDRESS_NAME_2 $    ADDRESS_NAME_3 $    CITY $  province $  POSTAL_CODE $   home_no $   work_no $   MKT_ACCOUNT_TYPE $  ESN_EFFECTIVE_DATE $    CABLE_FOOTPRINT $   COMMON_IND $    CS_VIP_CLASS $  OPT2 $  OPT6 $  OPT7 $  KEYCODE $   CAMPAIGN_CODE $     CAMPAIGN_CYCLE $    CAMPAIGN_DATE $     CAMPAIGN_DESCRIPTION $  CAMPAIGN_TYPE $     EMAIL $     MOP $   SERIAL_NUMBER $     ACTIVATION_SUB_TYPE $   SALES_REP $ ;
      run;      data import;
        set %if ne 1 %then import;
            &name;
      run;
      %let i = %eval(&i+1);
      filename  file&i clear;  
Richard ответил: 28 апреля 2018 в 11:26
Это будет работать, только если данные Excel будут экспортированы в файл csv.
Pari T ответил: 28 апреля 2018 в 12:15
Спасибо! На этой веб-странице, которую вы связали, есть информация об изменении параметров реестра Windows, когда у офиса есть версия 2013 или около того, но нет информации для версии 2016. Версия моего офиса - 2016 год, знаете ли вы, как можно изменить настройку реестра?
Richard ответил: 28 апреля 2018 в 03:00
Вы можете совать в RegEdit или использовать функцию RegEdit Find, чтобы найти запись для TypeGuessingRows.