topic Re: Datatype error while stacking files and comparing using proc import in SAS Programming

Datatype error while stacking files and comparing using proc import

User_2024 — Fri, 01 Mar 2024 19:23:46 GMT

Headsup ! I am a beginner in sas. Appreciate your time, kindness and help!

I have a master dataset and three sub datasets ,with various columns puled from Oracle database. The code works till I pull the data, merge. I run this everyday, the output is an excel file with acct_num, acct_id and other columns. the logic looks back last 7 days and fetches the result.

Now, the tricky part is I am comparing the today's file to last 7 days and remove duplicates from today's report.( My previous post was to seek help on defining &yesterday, and with the help I created &previous_day 1 to 7 , stacked)
After the comparison runs and when I pull the final data, I have this datatype error that seems to be difficult to resolve. I used APPEND, APPEND with FORCE, I also tried creating TEST dataset so my first previous_day1 report will look at test and avoid any data type error IF the previous report has ANY NULL data.

253 data myora.final_result2;
254 /*format open_dt 10. close_dt 10. merch_num 12. DOB $10.;*/
255 merge myora.final_result (in=a) Previous_report1(in=b) Previous_report2 (in=c)
255! Previous_report3 (in=d) Previous_report4 (in=e) Previous_report5 (in=f) Previous_report6
255! (in=g) Previous_report7(in=h);
ERROR: Variable OPEN_DT has been defined as both character and numeric.
ERROR: Variable CLOSE_DT has been defined as both character and numeric.
ERROR: Variable MERCH_NUM has been defined as both character and numeric.
ERROR: Variable DOB has been defined as both character and numeric.
ERROR: Variable ACCT_NUM has been defined as both character and numeric

%let current_day = %sysfunc(today());

%let weekday = %sysfunc(weekday(&current_day.));

%if &weekday. = 2 %then %do;

/* If it's Monday, fetch friday */

%let previous_day = %sysfunc(intnx(day, &current_day., -3), date9.);

%let previous_day2 = %sysfunc(intnx(day, &current_day., -4), date9.);

%let previous_day3 = %sysfunc(intnx(day, &current_day., -5), date9.);

%let previous_day4 = %sysfunc(intnx(day, &current_day., -6), date9.);

%let previous_day5 = %sysfunc(intnx(day, &current_day., -7), date9.);

%let previous_day6 = %sysfunc(intnx(day, &current_day., -8), date9.);

%let previous_day7 = %sysfunc(intnx(day, &current_day., -9), date9.);

%end;

%else %do;

/* For the rest of the week, refer to yesterday */

%let previous_day = %sysfunc(intnx(day, &current_day., -1), date9.);

%let previous_day2 = %sysfunc(intnx(day, &current_day., -2), date9.);

%let previous_day3 = %sysfunc(intnx(day, &current_day., -3), date9.);

%let previous_day4 = %sysfunc(intnx(day, &current_day., -4), date9.);

%let previous_day5 = %sysfunc(intnx(day, &current_day., -5), date9.);

%let previous_day6 = %sysfunc(intnx(day, &current_day., -6), date9.);

%let previous_day7 = %sysfunc(intnx(day, &current_day., -7), date9.);

%end;

%put &previous_day.;

PROC IMPORT

DATAFILE = "filepath\Output\filename&previous_day..xlsx"

OUT = Previous_report1

DBMS = EXCEL REPLACE;

RANGE = 'possible_accts$';

MIXED = YES;

SCANTEXT = YES;

USEDATE = YES;

SCANTIME = YES;

RUN;

/*proc print data = Previous_report1;run;*/

PROC IMPORT

DATAFILE = "filepath\Output\filename&previous_day2..xlsx"

OUT = Previous_report2

DBMS = EXCEL REPLACE;

RANGE = 'possible_accts$';

MIXED = YES;

SCANTEXT = YES;

USEDATE = YES;

SCANTIME = YES;

RUN;

/*proc print data = Previous_report2;run;*/

PROC IMPORT

DATAFILE = "filepath\Output\filename&previous_day3..xlsx"

OUT = Previous_report3

DBMS = EXCEL REPLACE;

RANGE = 'possible_accts$';

MIXED = YES;

SCANTEXT = YES;

USEDATE = YES;

SCANTIME = YES;

RUN;

PROC IMPORT

DATAFILE = "filepath\Output\filename&previous_day4..xlsx"

OUT = Previous_report4

DBMS = EXCEL REPLACE;

RANGE = 'possible_accts$';

MIXED = YES;

SCANTEXT = YES;

USEDATE = YES;

SCANTIME = YES;

RUN;

PROC IMPORT

DATAFILE = "filepath\Output\filename&previous_day5..xlsx"

OUT = Previous_report5

DBMS = EXCEL REPLACE;

RANGE = 'possible_accts$';

MIXED = YES;

SCANTEXT = YES;

USEDATE = YES;

SCANTIME = YES;

RUN;

PROC IMPORT

DATAFILE = "filepath\Output\filename&previous_day6..xlsx"

OUT = Previous_report6

DBMS = EXCEL REPLACE;

RANGE = 'possible_accts$';

MIXED = YES;

SCANTEXT = YES;

USEDATE = YES;

SCANTIME = YES;

RUN;

PROC IMPORT

DATAFILE = "filepath\Output\filename&previous_day7..xlsx"

OUT = Previous_report7

DBMS = EXCEL REPLACE;

RANGE = 'possible_accts$';

MIXED = YES;

SCANTEXT = YES;

USEDATE = YES;

SCANTIME = YES;

RUN;

proc delete data=test;run;

data test;

/* Define variable lengths */

format acct_num 16. acct_id 8. tran_dt 12. open_dt $10. close_dt 10. cheque_pymt 30. merch_num 12. postal_cd $6. count_cash_adv 4. total_cash_adv 9. name $15. DOB $10. email $24. tel_home 10. addr_ln1 $17.;

run;

rsubmit;

proc upload data=test;

run;

proc sort data=test;

by acct_id;

run;

proc upload data=Previous_report1 ;

run;

proc sort data=Previous_report1;

by acct_id;

run;

proc upload data=Previous_report2 ;

run;

proc sort data=Previous_report2;

by acct_id;

run;

proc upload data=Previous_report3 ;

run;

proc sort data=Previous_report3;

by acct_id;

run;

proc upload data=Previous_report4 ;

run;

proc sort data=Previous_report4;

by acct_id;

run;proc upload data=Previous_report5 ;

run;

proc sort data=Previous_report5;

by acct_id;

run;

proc upload data=Previous_report6 ;

run;

proc sort data=Previous_report6;

by acct_id;

run;

proc upload data=Previous_report7 ;

run;

proc sort data=Previous_report7;

by acct_id;

run;

endrsubmit;

proc append base=test data=Previous_report1 FORCE;run;

/* try to run it without FORCE and then with force */

proc append base=test data=Previous_report2 FORCE;run;

proc append base=test data=Previous_report3 FORCE;run;

proc append base=test data=Previous_report4 FORCE;run;

proc append base=test data=Previous_report5 FORCE;run;

proc append base=test data=Previous_report6 FORCE;run;

proc append base=test data=Previous_report7 FORCE;run;

rsubmit;

proc delete data= myora.final_result2;run;

data myora.final_result2;

merge myora.final_result (in=a) Previous_report1(in=b) Previous_report2 (in=c) Previous_report3 (in=d) Previous_report4 (in=e) Previous_report5 (in=f) Previous_report6 (in=g) Previous_report7(in=h);

by acct_id ;

if a and not b and not c and not d and not e and not f and not g and not h;

run;

endrsubmit;

rsubmit; /*table for result */

proc sql;

connect to oracle(&ora_str.);

create table final as

select * from connection to oracle(

select ACCT_NUM,ACCT_ID, CHEQUE_PYMT,NAME, DOB, EMAIL, TEL_HOME, ADDR_LN1

from final_result2 where CHEQUE_PYMT >= '1000.00'

);

disconnect from oracle;

quit;

endrsubmit;

rsubmit;

proc download data=final;

run;

endrsubmit;

Re: Datatype error while stacking files and comparing using proc import

Kurt_Bremser — Fri, 01 Mar 2024 19:44:04 GMT

If you need consistency with regards to data types and other variable attributes, then you MUST NOT use PROC IMPORT, which also means that you MUST NOT use Excel files as data source.

All means that SAS provides for reading Excel files involve guessing and are notoriously unreliable.

From where do these Excel files come?

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Fri, 01 Mar 2024 19:52:00 GMT

Ah... ok. So, I generate the output using EXCEL files. and this has to come out everyday for reviewing purposes with acct_num and other demographic info. Ideally my problem is todays_report should not have ANY entries /duplicates from yesterdays or day before. the code works if I compare todays report to yesterdays and THEN PRINT todays BUT if there are Zero records for today then it pulls data from day before yesterday's which is such a pain now.

Re: Datatype error while stacking files and comparing using proc import

ballardw — Fri, 01 Mar 2024 20:08:49 GMT

@User_2024 wrote:

Ah... ok. So, I generate the output using EXCEL files. and this has to come out everyday for reviewing purposes with acct_num and other demographic info. Ideally my problem is todays_report should not have ANY entries /duplicates from yesterdays or day before. the code works if I compare todays report to yesterdays and THEN PRINT todays BUT if there are Zero records for today then it pulls data from day before yesterday's which is such a pain now.

IF you are generating the Excel files as output are you doing it from SAS? If so, then the place to start would be the data sets used to create that Excel output, not rereading output.

FWIW in a typical week I read anywhere from 10 to 50 files that start out as XLSX. Because of the headaches involved with that I save them to CSV and then can use a data step to read the files into consistent variables by type, length and name.(Until the idiots providing the source change the column order, then I need to change the order of the Input statement I use.)

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Fri, 01 Mar 2024 20:17:35 GMT

ohh. thats a great point, I am just starting to create reports using SAS. And yes, you are right, i use SAS to create the XLSX report. can you throw me a sample how I can convert this to CSV now ? and all my previous 7 days files are also in XLSX. How to fix them now ? this is how I do it. any insights are sincerely appreciated .

proc export
data=final outfile="filepath\Output\filename&today..xlsx"
dbms=xlsx replace;
sheet="data_for_review";
run;

Re: Datatype error while stacking files and comparing using proc import

Kurt_Bremser — Fri, 01 Mar 2024 20:38:42 GMT

Keep the data in SAS. Create those Excel reports for reviewing, but keep the underlying data as SAS datasets for further processing.

Re: Datatype error while stacking files and comparing using proc import

ballardw — Fri, 01 Mar 2024 20:46:15 GMT

@User_2024 wrote:
ohh. thats a great point, I am just starting to create reports using SAS. And yes, you are right, i use SAS to create the XLSX report. can you throw me a sample how I can convert this to CSV now ? and all my previous 7 days files are also in XLSX. How to fix them now ? this is how I do it. any insights are sincerely appreciated .

proc export
data=final outfile="filepath\Output\filename&today..xlsx"
dbms=xlsx replace;
sheet="data_for_review";
run;

I would create a permanent library for the data related to this project. Then as you make a "final" data set either append it to a long-term set in that library (best if the contents don't change often) or, and I know this is unpopular for many reasons, make a copy in that permanent library with the date in the name such as Final_20240301. This may be better if the contents do change, meaning the number of variables, the names of the variables and metadata.

Then combine the data sets as needed (if the append approach isn't feasible and may not be with a history of Proc Import...)

Re: Datatype error while stacking files and comparing using proc import

Patrick — Sun, 03 Mar 2024 01:30:40 GMT

@User_2024 Looking at the code you share I feel it might be worth to take a step back and look at your whole process and potentially revamp it.

"I have a master dataset and three sub datasets ,with various columns puled from Oracle database. "

1. Is all your data in Oracle or only some additional information that you use to enrich your master dataset?

2. Where is your master dataset stored?

"Now, the tricky part is I am comparing the today's file to last 7 days and remove duplicates from today's report."

3. What constitutes a "duplicate" and why do you get them in first place?

4. Are there updates to your source data between your daily queries (like a change to open_dt)?

6. What's the purpose of these rsubmit in your code. Why do you need them?

And as others already stated:

Do not use Excel as a data source. Easiest would be to store the daily SAS table permanently that you use to create the Excel and then use these SAS tables the next day.

You could either create daily tables with a date portion in the table name or append daily data to a single table with a date column.

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Mon, 04 Mar 2024 13:13:06 GMT

1. Is all your data in Oracle or only some additional information that you use to enrich your master dataset? - All of my data are from Oracle sql.

2. Where is your master dataset stored? - its in myora. and not in WORK.

6. What's the purpose of these rsubmit in your code. Why do you need them?- I always use them for ease of execution. Since am a beginnner in sas, I am open for suggestions and ideas, this will be my first ever report creation in SAS. Appreciate your insights and time!

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Mon, 04 Mar 2024 13:13:39 GMT

Thanks, could you point me for an example pls?

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Mon, 04 Mar 2024 13:14:27 GMT

Thank you, any sample syntax or code pls ? Appreciate your time.

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Mon, 04 Mar 2024 13:26:24 GMT

3. What constitutes a "duplicate" and why do you get them in first place? - i run this report on weekdays and i use the date constraint to look at last 7 days of data TRANSACTION_DT < TRUNC(SYSDATE) -7 so for example when the report runs today, it picks up records from last 7 days and has duplicates in it, so I did a proc import to compare yesterdays to today to remove dupes, which works for one day, BUT if today;s report has NO DATA in it, it goes and picks up day before yesterdays data as output. So to avoid that I started stacking last 7 days of output files, sorted by acct_id and compared with today's , removed duplicates. but at this point, its not working because of the data type error.

4. Are there updates to your source data between your daily queries (like a change to open_dt)?- no, open_dt is the date when the account was opened so no change in it, the only change is at the Transaction_dt as the real time transaction happens,

Re: Datatype error while stacking files and comparing using proc import

Patrick — Mon, 04 Mar 2024 23:54:18 GMT

@User_2024

"and i use the date constraint to look at last 7 days of data TRANSACTION_DT < TRUNC(SYSDATE) -7"
Above selection would return all rows with a transaction date OLDER than 7 days.

To select the past 7 days (without the current day where you won't have all transactions):

TRANSACTION_DT between TRUNC(SYSDATE) -7 and TRUNC(SYSDATE) -1

Assuming you don't have duplicate transactions in your source, your definition of duplicates doesn't make much sense to me.
It feels this is more an issue with your selection logic and not with your data.
If you just want to select yesterday's transactions change your selection to:

TRANSACTION_DT = TRUNC(SYSDATE) -1

As I understand it your report is at account level and not at transaction level so at some point you're aggregating the data.

Why do you need transaction level data at all? Like: Is this some summary of transactions per account over the last 7 days and you only want to include accounts that had a change yesterday? If so then it wouldn't be hard to identify all accounts with a change yesterday directly on the Oracle side and only create account level aggregated rows moved to SAS that meet this condition.

You just need to describe a bit more in detail what you have and what your end result needs to be.

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Wed, 06 Mar 2024 18:16:45 GMT

Thank you for suggesting. I am trying to find payments made in last 7 days, and I need to run this daily, When I do this I stumbled into duplicates because data from say for example from Mar3 shows up in MAr4 when I run it, and it also shows up when I run on Mar5. And I gotta look at last 7 days because there could be different payment type like cheques.

To resolve it, I imported yesterday's output file, compared to today's, removed duplicates, which worked fine.

But, when I did that for multiple days, I faced datatype error and that when I approached the community. Hope, this helps.

Issues were on defining today, yesterday macros that got fixed from help here. And finally resolved the datatype error by keeping just the index variable while stacking.

proc upload data=Previous_report1 (keep=acct_id);
run;
proc sort data=Previous_report1;
by acct_id;
run;
proc upload data=Previous_report2 (keep=acct_id);
run;
proc sort data=Previous_report2;
by acct_id;
run;

Re: Datatype error while stacking files and comparing using proc import

Patrick — Wed, 06 Mar 2024 23:29:46 GMT

@User_2024 wrote:

Thank you for suggesting. I am trying to find payments made in last 7 days, and I need to run this daily, When I do this I stumbled into duplicates because data from say for example from Mar3 shows up in MAr4 when I run it, and it also shows up when I run on Mar5. And I gotta look at last 7 days because there could be different payment type like cheques.

To resolve it, I imported yesterday's output file, compared to today's, removed duplicates, which worked fine.

But, when I did that for multiple days, I faced datatype error and that when I approached the community. Hope, this helps.

Yes, of course, if you run daily but select the last 7 days of transactions then you will select the same transaction on multiple days.

"And I gotta look at last 7 days because there could be different payment type like cheques."

And you want to report (select) this check payment now for 7 days or only once?

I still believe that ideally you would write logic that does the desired selection directly on the database instead of reading and processing some .csv or Excel for post processing that you created via runs on past days. Just an opinion.

Re: Datatype error while stacking files and comparing using proc import

Patrick — Sat, 09 Mar 2024 02:31:51 GMT

@User_2024 I felt I should share your reply to our private message here. I hope you don't mind.

Thank you for the suggestion, I will keep the discussions in public for all my posts from now on. But, let me answer for the questions here,

Is my understanding correct that in your master table you've got transactional data where in a single row you've got the customer_id, transaction type and transaction date? - Yes, your understanding is correct.

Let's say you're running your program on a Wednesday: Do I understand this right that the only customers you'd want to report on are the ones that had a type1 transaction on Tuesday (your most current data) and never had such a type 1 transaction on any earlier date? --- That would be correct, sometimes the payment table gets updated later in the day which "may" miss that days data if i run the job in the morning. That is why I always look back for last 5 weekdays.

If you run your program on a Monday then you would select the previous Friday-Sunday because the last report you run on Friday only included data up-to Thursday. - this is accurate. And, I will run only on weekdays, but if we have a public holiday on a weekday, I think I have to think about how other similar jobs are set up among my team mates.

Re: Datatype error while stacking files and comparing using proc import

Patrick — Sat, 09 Mar 2024 02:36:55 GMT

@User_2024 Based on what you've told me below some "template" sample code to show how logic could work only using your one main table in Oracle.

I would only switch to something more "complicated" for performance reasons - like maintaining a separate table with customer ids selected in earlier runs instead of working this out every single time like in below query.

options dlcreatedir;
libname ora  "%sysfunc(pathname(work))/oracle";
libname ctrl "%sysfunc(pathname(work))/perm_storage";

/* master source table with all transactions */
data ora.master;
  infile datalines truncover dlm=',';
  input trans_id acct_id$ cust_id$ trans_type trans_dttm:anydtdtm.;
  format trans_dttm datetime20.;
  datalines;
1,a,x,1,01jan2024
2,a,x,2,01feb2024
3,b,x,1,01mar2024
2,a,x,2,02mar2024
2,b,x,2,02mar2024
;

/* control table to store max transaction date selected in a previous run */
/* - needs to be a permanent table    
   - create table if it doesn't exist 
*/
%if not %sysfunc(exist(ctrl.last_selected)) %then
  %do;
    data ctrl.last_selected;
      attrib run_dttm max_trans_dttm format=datetime20.;
      stop;
    run;
  %end;

/* &prev_max_trans_dttm: max transaction date already selected in a previous run */
proc sql noprint;
  select coalesce(max(max_trans_dttm),0) format=datetime20. into :prev_max_trans_dttm trimmed
  from ctrl.last_selected
  ;
quit;


/* create data for reporting:
   - only include transactions of type 1
   - only select transaction dates > &prev_max_trans_dttm
   - exclude transactions that already had been selected in earlier runs
      = transactions for customers that already had a type 1 transaction at or before &prev_max_trans_dttm
*/
proc sql;
  create table work.report_source as
    select cust_id, trans_type, trans_dttm
      from
        (
          select cust_id, trans_type, trans_dttm
            from ora.master
              where trans_dttm>"&prev_max_trans_dttm"dt and trans_type=1
                group by cust_id
                  having max(trans_dttm)=trans_dttm
        )
          where not exists
            (
              select distinct cust_id
                from ora.master
                  where trans_dttm<="&prev_max_trans_dttm"dt and trans_type=1
            )
  ;
quit;

/* add most current max transaction date selected to control table */
proc sql;
  insert into ctrl.last_selected
    set run_dttm=datetime(), max_trans_dttm=max("&prev_max_trans_dttm"dt,(select max(trans_dttm) from report_source))
  ;
quit;


/* and here just some "template" to give you an idea how to deal with a zero row result */
data _null_;
  call symputx('nobs',nobs);
  stop;
  set work.report_source nobs=nobs;
run;
%put &=nobs;

%if &nobs=0 %then
  %do;
    data _null_;
      file print;
      put "no rows selected";
    run;
  %end;
%else
  %do;
    proc print data=work.report_source;
    run;
  %end;

Re: Datatype error while stacking files and comparing using proc import

User_2024 — Mon, 11 Mar 2024 11:42:38 GMT

Thank you for the suggestion, I will give it a try and post here on the updates.