assigning different datatype to dataset variables during proc import

[SASUserRocks]

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.

[smantha · Accepted solution]

%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;

[ballardw]

---

@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.

[smantha]

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 

[ballardw]

---

@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

 <put your varlist here>

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.

[SASUserRocks]

Hi @smantha  

I tried above and temp.csv is getting created. Only error now am receiving is :-

ERROR: Variable DSLV_personal_finance_UAENation has been defined as both character and numeric.

This is my actual requirement, there are many such DSLV fields that may come blank and taken as character field by SAS import. These DSLV fields i want in numeric datatype only. Please help.

[Tom]

Make sure the FILE statement and the INFILE statement refer to the same name, either the CSV fileref defined in the FILENAME statement or a quoted physical filename.

Did you have a duplicate name in your list of variable lengths?  Check that. Each variable need to have its own name. If you run the code without the macro first it is easier to debug because the line numbers in the error messages will match the line numbers in the log. Once you get it working then wrap it into the macro definition.

[SASUserRocks]

My code as of now:-

libname DAshare2 '/sasdata/DataAnalytics/'; /** Assigning Library **/

%macro abc;
options validvarname=any;

proc import replace datafile= "/SASAnalytics/DA//Daily_File_20200516.xlsx"
out=DAShare2.DSN_TEMP
DBMS=XLSX;
run;
proc export data=DAShare2.DSN_TEMP 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 cookie $28.;
informat date $10.;
informat timestamp $28.;
informat Last_url_visited $80.;
informat ip $28.;
informat stage $28.;
informat device $28.;
informat os $28.;
informat browser $28.;
informat Page $28.;
informat Product $28.;
informat SubProduct $28.;
informat Pname $28.;
informat cookieBirth 6.2;
informat typnmarr $28.;
informat dslv 6.2;
informat last_10_urls_visited $400.;
informat taxonomy $28.;
informat dsdo 6.2;
informat clickid $28.;
informat DSLV_cards 6.2;
informat DSLV_accounts 6.2;
informat DSLV_loans 6.2;
informat DSLV_personal_loan_expats 6.2;
informat DSLV_bca_silver 6.2;
informat DSLV_betaqti_credit_card 6.2;
informat DSLV_etihad_GA_account 6.2;
informat DSLV_credit_cards 6.2;
informat DSLV_touchpoint_ic 6.2;
informat DSLV_privilege_C_Plat_DC 6.2;
informat DSLV_millionaire_D_Saving_Acc 6.2;
informat DSLV_traveller_credit_card 6.2;
informat DSLV_adcb_titanium_debit_card 6.2;
informat DSLV_touchpoint_platinum_cc 6.2;
informat DSLV_fixed_deposit_account 6.2;
informat DSLV_active_saver_account 6.2;
informat DSLV_excellence_plat_DC 6.2;
informat DSLV_etihad_guest_Abo_Exce 6.2;
informat DSLV_personal_loan_uaeNationl 6.2;
informat DSLV_etihad_guest_Infin_CC 6.2;
informat DSLV_personal_finance_for_expats 6.2;
informat DSLV_personal_finance_UAENation 6.2;
informat DSLV_etihad_guest_privilege_acc 6.2;
informat DSLV_etihad_guest_excellency_acc 6.2;
informat DSLV_etihad_platinum_credit_card 6.2;
informat DSLV_etihad_guest_accounts 6.2;

infile "temp.csv" delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
input cookie
dslv;
set DAShare2.DSN_temp;
run;
%mend;
%abc;

[Tom]

INFORMAT is not intended to be used to DEFINE variables. It is instructions for how to READ the variable from TEXT. It will only define the variables as a side effect of being the first place you reference the variable in the data step, the same as side effect of using the variable in most other statements, like assignment statement.  Note that for most variable there is no need to attach any INFORMAT to the variable. SAS already knows how to read character strings and numbers from text.   The exception are things like DATE, TIME and DATETIME values where the text used to represent the value is totally different than the value that is stored in the variable. (see next point)

Do NOT use decimal part on an INFORMAT.  That is instructions to SAS for how to handle input strings without an explicit decimal place. So if you use an informat of 6.2 you are telling SAS to divide text that looks like an integer by 100.  So '123456' will be come the number 1,234.56 instead of the number 123,456.

I think you might have meant to use a positional variable list in your INPUT statement.  Something like:

input cookie -- DSLV_etihad_guest_accounts ;

Note that once the type of the variable has been defined there is no need to add an informat into the INPUT statement.  And if you do need to add an informat into the INPUT statement and you are reading from a delimited file then make sure to use the colon modifier before the informat to prevent SAS from reading the exact number of characters the informat spec says, even if it is longer (or shorter) than the next delimited value.