topic Re: assigning different datatype to dataset variables during proc import in SAS Programming

assigning different datatype to dataset variables during proc import

SASUserRocks — Thu, 11 Jun 2020 07:45:59 GMT

Hi,

I am trying to do proc import from Excel workbook to load some empty fields which will be later added with integer values. The dataset is guessing rows in a way that string datatype is assigned to these variables. Please help.

Thanks

Anjali

Re: assigning different datatype to dataset variables during proc import

Kurt_Bremser — Thu, 11 Jun 2020 08:12:53 GMT

Save the required sheet(s) to csv file(s), and read those with a data step where you have full control over the import process.

Re: assigning different datatype to dataset variables during proc import

Jagadishkatam — Thu, 11 Jun 2020 08:13:12 GMT

Please try to use the mixed=yes option to avoid the issue

PROC IMPORT OUT= mydata
     DATAFILE= "J:\users\nsouli\Debugging.xls" 
     DBMS=EXCEL REPLACE;
     SHEET="'SAS$'"; 
     GETNAMES=YES;
     MIXED=yes;     
RUN;

Re: assigning different datatype to dataset variables during proc import

ed_sas_member — Thu, 11 Jun 2020 08:14:56 GMT

Hi @SASUserRocks

You can try to convert your excel file in csv / txt. Then you can use a DATA step to import your file and specify the type of your variables with the correct informat in an INPUT statement.

Best,

Re: assigning different datatype to dataset variables during proc import

SASUserRocks — Thu, 11 Jun 2020 08:54:01 GMT

There are huge number of xlsx files that I will receive everyday and not willing to create overhead at SAS server.

Re: assigning different datatype to dataset variables during proc import

SASUserRocks — Thu, 11 Jun 2020 08:56:47 GMT

@Jagadishkatam Tried below code and its throwing error "ERROR: DBMS type EXCEL not valid for import."

proc import replace datafile= "/SASAnalytics/DA/Daily_File_20200516.xlsx"
out= DAShare2.Test
dbms=excel ;
DBDSOPTS= "DBTYPE=(DSLV_personal_finance_UAENation='NUM(8)')";
MIXED=yes;

run;

Re: assigning different datatype to dataset variables during proc import

SASUserRocks — Thu, 11 Jun 2020 09:01:52 GMT

@Kurt_Bremser There are around 200 xlsx , I wont be able to manually convert them to csv.

Re: assigning different datatype to dataset variables during proc import

Kurt_Bremser — Thu, 11 Jun 2020 09:21:48 GMT

@SASUserRocks wrote:

@Kurt_Bremser There are around 200 xlsx , I wont be able to manually convert them to csv.

Using the worst imaginable file format for mass data transfer is at best stupid. No matter what you do, you end up with manual conversions and/or fixes, because every single import will end up with more or less subtle differences.

You might be able to automate a xlsx to csv conversion with LibreOffice or VBA.

Have the data sent in a sensible format in the first place.

Excel is only good for manual tinkering, not for automatable (and reliably automatable) processes.

Re: assigning different datatype to dataset variables during proc import

smantha — Thu, 11 Jun 2020 11:22:15 GMT

Anjali,

You keep mentioning large amount of excel files is it 100s, 1000s. I hope you do not process them manually. Unfortunately SAS has minimal control on reading data from excel as opposed to other formats. If you know what the fields are and what the formats should be you can do explicit conversion after reading it into a sas dataset.

Re: assigning different datatype to dataset variables during proc import

SASUserRocks — Thu, 11 Jun 2020 13:42:07 GMT

Hi @smantha , Currently its 200, but this is daily incremental file that keeps coming till future date. I am trying below code to work.

options validvarname=any;
%macro abc;
proc import replace datafile= "/SASAnalytics/DA/Weblog_Lemnisk/ProdData/NewSet/Daily_File_20200516.xlsx"
out=DAShare2.DSN_TEMP
DBMS=XLSX;

%let varlist= cookie $20 date $10 timestamp $20 Last_url_visited $800 ip $20 stage $20 device $20 os $20 browser $20 Page $120 Product $120
SubProduct $120 Pname $120 cookieBirth $20 typnmarr $120 dslv 5 last_10_urls_visited $400 taxonomy $400 dsdo 20 clickid $120 DSLV_cards 20
DSLV_accounts 20 DSLV_loans 20 DSLV_personal_loan_expats 20 DSLV_bca_silver 20 DSLV_betaqti_credit_card 20 DSLV_etihad_GA_account 20
DSLV_credit_cards 20 DSLV_touchpoint_ic 20 DSLV_privilege_C_Plat_DC 20 DSLV_millionaire_D_Saving_Acc 20 DSLV_traveller_credit_card 20
DSLV_adcb_titanium_debit_card 20 DSLV_touchpoint_platinum_cc 20 DSLV_fixed_deposit_account 20 DSLV_active_saver_account 20 DSLV_excellence_plat_DC 20
DSLV_etihad_guest_Abo_Exce 20 DSLV_personal_loan_uaeNationl 20 DSLV_etihad_guest_Infin_CC 20 DSLV_personal_finance_for_expats 20
DSLV_personal_finance_UAENation 20 DSLV_etihad_guest_privilege_acc 20 DSLV_etihad_guest_excellency_acc 20 DSLV_etihad_platinum_credit_card 20
DSLV_etihad_guest_accounts 20;

data dsn;
  length &varlist ;
  set DAShare2.DSN_temp;
  format _all_;
run;
%mend;
%abc;

Re: assigning different datatype to dataset variables during proc import

smantha — Thu, 11 Jun 2020 13:48:42 GMT

First you can check if the formats of variable from the excel read is consistent with what you expect. If not then write out explicit conversions. 200 files is not that many, being xlsx i can guess at most a million rows, unless the number of sheets you read is more than 5. Can you request whoever is generating these files to write as csv?

Re: assigning different datatype to dataset variables during proc import

Kurt_Bremser — Thu, 11 Jun 2020 14:13:13 GMT

20 is not a valid length for numeric variables (maximum is 8). Your code will also not work if a column was imported with the wrong type (numeric vs. character).

I can only repeat: from 20+ years of SAS experience, using Excel files for this is madness.

Re: assigning different datatype to dataset variables during proc import

SASUserRocks — Thu, 11 Jun 2020 14:39:15 GMT

Accept that fact. We will get files in CSV format in future , until then I have to process xlsx files for the dashboard.

Re: assigning different datatype to dataset variables during proc import

ballardw — Thu, 11 Jun 2020 15:12:25 GMT

How similar in structure are these files? This means column order with same intended values/types?

How many header rows (a major bane of Proc Import)? Does this number change in files of the same "structure".

If the files have a similar structure you might be able to use either LIBNAME PCFILES or EXCEL to access the data. Then you would have meta data information available available from the SASHELP.Vcolumns or Dictionary.Columns when using Proc SQL. Then write/ modify code to create new SAS datasets as needed, variable names, types, labels and such using data step code. But if you have lots of different layouts this can get cumbersome because you will have to have code that examines pretty much each column of the spreadsheet with the desired type and implement the correct code for conversion.

The details are important though. If the column orders or numbers of header rows change then you can get into very complicated headaches in attempting to automate. I have one source of XLSX I have to deal with where the 1) column orders change from month to month, 2) the number of header rows can change from month to month and 3) the "values" of a file or personal identification value will change to DATE formats partway through the file. And I have no way I can think of that would "automate" that conversion with out testing every single value in every single column.

Re: assigning different datatype to dataset variables during proc import

SASUserRocks — Thu, 11 Jun 2020 15:29:14 GMT

Hi @ballardw

Files are of similar structure and i tried using dbms= EXCEL which is not working and have reported to SAS admin. In future i will get these files in csv,until then i have to live with xlsx

Now am trying to do below code work.

%macro abc;
proc options option=validvarname;
run;

proc import replace datafile= "/SASAnalytics/DA/Daily_File_20200516.xlsx"
out=DAShare2.DSN_TEMP
DBMS=XLSX;
%let varlist= cookie $28 date $10 timestamp $28 Last_url_visited $800 ip $28 stage $28 device $28 os $28 browser $28 Page $28 Product $28
SubProduct $120 Pname $8 cookieBirth $8 typnmarr $28 dslv 5 last_10_urls_visited $400 taxonomy $400 dsdo 8 clickid $28 DSLV_cards 8
DSLV_accounts 8 DSLV_loans 8 DSLV_personal_loan_expats 8 DSLV_bca_silver 8 DSLV_betaqti_credit_card 8 DSLV_etihad_GA_account 8
DSLV_credit_cards 8 DSLV_touchpoint_ic 8 DSLV_privilege_C_Plat_DC 8 DSLV_millionaire_D_Saving_Acc 8 DSLV_traveller_credit_card 8
DSLV_adcb_titanium_debit_card 8 DSLV_touchpoint_platinum_cc 8 DSLV_fixed_deposit_account 8 DSLV_active_saver_account 8 DSLV_excellence_plat_DC 8
DSLV_etihad_guest_Abo_Exce 8 DSLV_personal_loan_uaeNationl 8 DSLV_etihad_guest_Infin_CC 8 DSLV_personal_finance_for_expats 8
DSLV_personal_finance_UAENation 8 DSLV_etihad_guest_privilege_acc 8 DSLV_etihad_guest_excellency_acc 8 DSLV_etihad_platinum_credit_card 8
DSLV_etihad_guest_accounts 8;
data dsn;
length &varlist ;
set DAShare2.DSN_temp;
format _all_;
run;
%mend;
%abc;

This is throwing error :-
ERROR: The value 'LET VARLIST'n is not a valid SAS name.
WARNING: Apparent invocation of macro LET VARLIST not resolved.
180: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
ERROR: Symbolic variable name VARLIST must contain only letters, digits, and underscores.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.

Re: assigning different datatype to dataset variables during proc import

smantha — Thu, 11 Jun 2020 15:37:24 GMT

%macro abc;
proc options option=validvarname;
run;

proc import replace datafile= "/SASAnalytics/DA/Daily_File_20200516.xlsx"
out=DAShare2.DSN_TEMP
DBMS=XLSX;
run;
proc export data=NEW dbms=csv

outfile="temp.csv"

replace;

run;
%let varlist= cookie $28 date $10 timestamp $28 Last_url_visited $800 ip $28 stage $28 device $28 os $28 browser $28 Page $28 Product $28
SubProduct $120 Pname $8 cookieBirth $8 typnmarr $28 dslv 5 last_10_urls_visited $400 taxonomy $400 dsdo 8 clickid $28 DSLV_cards 8
DSLV_accounts 8 DSLV_loans 8 DSLV_personal_loan_expats 8 DSLV_bca_silver 8 DSLV_betaqti_credit_card 8 DSLV_etihad_GA_account 8
DSLV_credit_cards 8 DSLV_touchpoint_ic 8 DSLV_privilege_C_Plat_DC 8 DSLV_millionaire_D_Saving_Acc 8 DSLV_traveller_credit_card 8
DSLV_adcb_titanium_debit_card 8 DSLV_touchpoint_platinum_cc 8 DSLV_fixed_deposit_account 8 DSLV_active_saver_account 8 DSLV_excellence_plat_DC 8
DSLV_etihad_guest_Abo_Exce 8 DSLV_personal_loan_uaeNationl 8 DSLV_etihad_guest_Infin_CC 8 DSLV_personal_finance_for_expats 8
DSLV_personal_finance_UAENation 8 DSLV_etihad_guest_privilege_acc 8 DSLV_etihad_guest_excellency_acc 8 DSLV_etihad_platinum_credit_card 8
DSLV_etihad_guest_accounts 8;
data dsn;
informat <put your varlist here> ;
infile "test.csv" <options> ;
input <put your varlist here> ;
set DAShare2.DSN_temp;
 run;
%mend;
%abc;

Re: assigning different datatype to dataset variables during proc import

ballardw — Thu, 11 Jun 2020 15:51:32 GMT

@smantha wrote:

%macro abc;
proc options option=validvarname;
run;

proc import replace datafile= "/SASAnalytics/DA/Daily_File_20200516.xlsx"
out=DAShare2.DSN_TEMP
DBMS=XLSX;
run;
proc export data=NEW dbms=csv

outfile="temp.csv"

replace;

run;
%let varlist= cookie $28 date $10 timestamp $28 Last_url_visited $800 ip $28 stage $28 device $28 os $28 browser $28 Page $28 Product $28
SubProduct $120 Pname $8 cookieBirth $8 typnmarr $28 dslv 5 last_10_urls_visited $400 taxonomy $400 dsdo 8 clickid $28 DSLV_cards 8
DSLV_accounts 8 DSLV_loans 8 DSLV_personal_loan_expats 8 DSLV_bca_silver 8 DSLV_betaqti_credit_card 8 DSLV_etihad_GA_account 8
DSLV_credit_cards 8 DSLV_touchpoint_ic 8 DSLV_privilege_C_Plat_DC 8 DSLV_millionaire_D_Saving_Acc 8 DSLV_traveller_credit_card 8
DSLV_adcb_titanium_debit_card 8 DSLV_touchpoint_platinum_cc 8 DSLV_fixed_deposit_account 8 DSLV_active_saver_account 8 DSLV_excellence_plat_DC 8
DSLV_etihad_guest_Abo_Exce 8 DSLV_personal_loan_uaeNationl 8 DSLV_etihad_guest_Infin_CC 8 DSLV_personal_finance_for_expats 8
DSLV_personal_finance_UAENation 8 DSLV_etihad_guest_privilege_acc 8 DSLV_etihad_guest_excellency_acc 8 DSLV_etihad_platinum_credit_card 8
DSLV_etihad_guest_accounts 8;
data dsn;
informat <put your varlist here> ;
infile "test.csv" <options> ;
input <put your varlist here> ;
set DAShare2.DSN_temp;
 run;
%mend;
%abc;

In an INFORMAT statement the format name must end with a period. So the VARLIST would need to be changed to work with INFORMAT.

Similarly on an input statement if you want an informat applied you need the period. However you would not want that on the actual input statement, just the names of the variables. The Informat would be applied. So the code needs two macro variables, one with the informat for use on that statement and the other without for use on the Input statement.

If the import/ export work properly I don't think you want to SET the data set created by Proc Import on the data step to read the csv.

Re: assigning different datatype to dataset variables during proc import

smantha — Thu, 11 Jun 2020 16:01:58 GMT

Thank you @ballardw for pointing out about the set in the data step. That is indeed my oversight. Other than that I agree with what you said about informats and inputs. That is why I put it in angular brackets on the informat and input statements leaving it as an exercise for @SASUserRocks. I am not sure about needing two macro variables if the list of variables and the order is consistent. @SASUserRocks might as well hard code them

Re: assigning different datatype to dataset variables during proc import

ballardw — Thu, 11 Jun 2020 16:23:41 GMT

@smantha wrote:

Thank you @ballardw for pointing out about the set in the data step. That is indeed my oversight. Other than that I agree with what you said about informats and inputs. That is why I put it in angular brackets on the informat and input statements leaving it as an exercise for @SASUserRocks. I am not sure about needing two macro variables if the list of variables and the order is consistent. @SASUserRocks might as well hard code them

You may want to test on a data set what happens when you indicate a format on the input statement with delimited data when the length of the values is less than the informat.

The Input statement in effect will override a default informat set with an Informat or Attribute statement, which can do very bad things to delimited data.

And the fix that would allow the informat on the Input statement would mean than an Informat statement was not needed.

The comment was based on the

in two places which can lead to improper use in at least one statement when coupled with the example varlist definition.

Consider these two data steps:

data example;
   infile datalines dlm=',' truncover;
   informat x $5. y $6.;
   input x y;
datalines;
abc,def
abcde,fghijk
;

data example2;
   infile datalines dlm=',' truncover;
   informat x $5. y $6.;
   input x  $5. y $6.;
datalines;
abc,def
abcde,fghijk
;

And see which result you actually might want.

Re: assigning different datatype to dataset variables during proc import

Tom — Thu, 11 Jun 2020 17:01:08 GMT

Close. Try this instead. Use PROC IMPORT to read it in. Use data step to write it out to temporary CSV file. Then use another data step to read in the data from the CSV file.

The VALIDVARNAME=ANY doesn't really do you any good, none of the names you want to use require it. The length for numeric variables should be 8 since SAS stores them as 8 byte floating point numbers. If you have any actual date, time or datetime values then you will want to add a FORMAT statement to the data step that creates the final dataset from the temporary csv file. But you don't need to write formatted values to the temporary CSV file, so no need for any INFORMAT statements.

%macro abc(infile,dsn);
* Import sheet ;
proc import  dbms=xlsx out=_temp_ replace
  datafile="/SASAnalytics/DA/Weblog_Lemnisk/ProdData/NewSet/&infile..xlsx"
;
run;
* Write to CSV file ;
filename csv temp;
data _null_;
  file csv dsd ;
  set _temp_;
  put (_all_) (+0);
  format _numeric_ best32. _character_;
run;

* Read from CSV file ;
data &dsn;
  length
    cookie $20
    date $10
    timestamp $20
    Last_url_visited $800
    ip $20
    stage $20
    device $20
    os $20
    browser $20
    Page $120
    Product $120
    SubProduct $120
    Pname $120
    cookieBirth $20
    typnmarr $120
    dslv 8
    last_10_urls_visited $400
    taxonomy $400
    dsdo 8
    clickid $120
    DSLV_cards 8
    DSLV_accounts 8
    DSLV_loans 8
    DSLV_personal_loan_expats 8
    DSLV_bca_silver 8
    DSLV_betaqti_credit_card 8
    DSLV_etihad_GA_account 8
    DSLV_credit_cards 8
    DSLV_touchpoint_ic 8
    DSLV_privilege_C_Plat_DC 8
    DSLV_millionaire_D_Saving_Acc 8
    DSLV_traveller_credit_card 8
    DSLV_adcb_titanium_debit_card 8
    DSLV_touchpoint_platinum_cc 8
    DSLV_fixed_deposit_account 8
    DSLV_active_saver_account 8
    DSLV_excellence_plat_DC 8
    DSLV_etihad_guest_Abo_Exce 8
    DSLV_personal_loan_uaeNationl 8
    DSLV_etihad_guest_Infin_CC 8
    DSLV_personal_finance_for_expats 8
    DSLV_personal_finance_UAENation 8
    DSLV_etihad_guest_privilege_acc 8
    DSLV_etihad_guest_excellency_acc 8
    DSLV_etihad_platinum_credit_card 8
    DSLV_etihad_guest_accounts 8
  ;
  infile csv dsd truncover ;
  input (_all_) (+0);
 run;
%mend;

*options validvarname=any;
%abc(Daily_File_20200516,dsn1)
%abc(Daily_File_20200517,dsn2)