topic Re: proc append while import CSV files in SAS Programming

proc append while import CSV files

Ronein — Fri, 04 Jun 2021 11:36:38 GMT

Hello

I am using proc append while import CSV files.

What is the reason that proc append is not working?

I am checking and dataset tbl_All was not created?

What is the correct code to do it please?


%macro import_one(YYYYMMDD);
%if %sysfunc(fileexist(/path/Revenue&YYYYMMDD.))
%then %do;
data tbl&YYYYMMDD.;
infile "path/Revenue&YYYYMMDD."
dlm=';' dsd truncover firstobs=2;
input VAR1-VAR59;
run;
%end;
%else %do;
data tbl&YYYYMMDD.;
Format VAR1-VAR59 8.;
stop;
run;
%end;

proc append base=tbl_All  data=tbl&YYYYMMDD. force; run; quit;
proc delete data=tbl&YYYYMMDD.;Run;

%mend import_one;
Data datestbl;
input YYYYMMDD $;
call execute('%nrstr(%import_one('!!YYYYMMDD!!'))');
cards;
20191231
20210101
20210102
20210103
20210104
20210105
;
Run;

Re: proc append while import CSV files

Kurt_Bremser — Fri, 04 Jun 2021 11:21:03 GMT

Maxim 2: Read the Log.

If that does not give you a sufficient clue, post the log here.

Re: proc append while import CSV files

Kurt_Bremser — Fri, 04 Jun 2021 11:24:28 GMT

The filenames are different here:

%if %sysfunc(fileexist(/path/RevenueA&YYYYMMDD.))
%then %do;
data tbl&YYYYMMDD.;
infile "path/Revenue&YYYYMMDD."

In the INFILE statement, there is no leading slash, and no A before the date, but both are present in the FILEEXIST call.

Re: proc append while import CSV files

ballardw — Fri, 04 Jun 2021 11:26:44 GMT

Log.

I do not see any file extension such as CSV in your FILEEXIST or INFILE statements. Which is pretty suspect.

Re: proc append while import CSV files

Ronein — Fri, 04 Jun 2021 11:34:56 GMT

NOTE: Appending WORK.tbl20210105 to WORK.tbl_ALL.
WARNING: Variable VAR1 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR2 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR3 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR4 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR5 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR6 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR7 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR8 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR9 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR10 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR11 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR12 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR13 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR14 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR15 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR16 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR17 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR18 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR19 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR20 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR21 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR22 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR23 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR24 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR25 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR26 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR27 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR28 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR29 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR30 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR31 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR32 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR33 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR34 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR35 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR36 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR37 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR38 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR39 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR40 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR41 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR42 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR43 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR44 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR45 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR46 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR47 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR48 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR49 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR50 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR51 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR52 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR53 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR54 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR55 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR56 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR57 was not found on BASE file. The variable will not be added to the BASE file.
21 The SAS System 13:55 Friday, June 4, 2021

WARNING: Variable VAR58 was not found on BASE file. The variable will not be added to the BASE file.
WARNING: Variable VAR59 was not found on BASE file. The variable will not be added to the BASE file.
NOTE: FORCE is specified, so dropping/truncating will occur.

INFO: Engine's fast-append process in use.
INFO: 14:30:58 Reading DATA file, updating BASE file
INFO: Starting data set size is 2 pages
INFO: Ending data set size is 2 pages, 0 added.
INFO: 00:00:00 Elapsed time
INFO: 14:30:58 Finished updating BASE file

NOTE: There were 3781 observations read from the data set WORK.tbl20210105.
NOTE: 3781 observations added.
NOTE: The data set WORK.tbl_ALL has 75625 observations and 0 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 1505.71k
OS Memory 26284.00k
Timestamp 06/04/2021 02:30:58 PM
Step Count 310 Switch Count 0
Page Faults 0
Page Reclaims 148
Page Swaps 0
Voluntary Context Switches 6
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

Re: proc append while import CSV files

Ronein — Fri, 04 Jun 2021 11:39:12 GMT

Thank you, I have changed the tables names because I cannot copy names from my work.

The import is running well .

IF the CSV file exists then a data set is created (with rows and columns).

IF the CSV file doesnt exist then an empty data set is created with 0 rows and columns .

In both cases columns are same (VAR-VAR59 with same attributes: all numerical).

The proc append is not working.

May anyone know why?

Re: proc append while import CSV files

Ronein — Fri, 04 Jun 2021 11:40:24 GMT

The import is working well.

The source files are without CSV end .

But they are with delimiter ;

As I said the import is working well but the proc append not

Re: proc append while import CSV files

Kurt_Bremser — Fri, 04 Jun 2021 11:48:56 GMT

You seem to already have a BASE dataset that does not contain your variables. Make sure that this dataset does not exist before you run your code.

Also, while developing/debugging, comment the PROC DELETE, so you can inspect the intermediate files.

But if your goal is to read a sequence of csv files into one dataset, you can do that in a single data step by using the FILEVAR= option of the INFILE statement and a loop for reading each file:

data want;
set datestbl;
fname = "/path/revenue" !! yymmdd;
if fileexist(fname)
then do;
  infile dummy filevar=fname dlm="," dsd truncover end=done;
  do until (done);
    input var1-var59;
    output;
  end;
end;
run;

Re: proc append while import CSV files

Tom — Fri, 04 Jun 2021 11:59:12 GMT

@Ronein wrote:

Thank you, I have changed the tables names because I cannot copy names from my work.

The import is running well .

IF the CSV file exists then a data set is created (with rows and columns).

IF the CSV file doesnt exist then an empty data set is created with 0 rows and columns .

In both cases columns are same (VAR-VAR59 with same attributes: all numerical).

The proc append is not working.

May anyone know why?

You need to make sure the empty datasets have the exact same structure as the other datasets.

I showed you how to modify the placement of the %IF block in the middle of the data step so that the empty datasets have the same structure as the other datasets in your other thread.

Re: proc append while import CSV files

Tom — Fri, 04 Jun 2021 16:15:30 GMT

To prevent the step from stopping on the first empty file switch from UNTIL() to WHILE().

do while (not done);

Re: proc append while import CSV files

Kurt_Bremser — Fri, 04 Jun 2021 17:41:41 GMT

You are perfectly right.

Re: proc append while import CSV files

Ronein — Sat, 05 Jun 2021 09:09:51 GMT

Now this is working,


%macro import_one(YYYYMMDD);
%if %sysfunc(fileexist(/path/Revenue&YYYYMMDD.))
%then %do;
data Revenue;
infile "path/Revenue&YYYYMMDD."
dlm=';' dsd truncover firstobs=2;
input VAR1-VAR59;
run;
%end;
%else %do;
data Revenue;
Format VAR1-VAR59 8.;
stop;
run;
%end;

proc append base=Revenue_All  data=Revenue  force; run; quit;
%mend import_one;
Data datestbl;
input YYYYMMDD $;
call execute('%nrstr(%import_one('!!YYYYMMDD!!'))');
cards;
20191231
20210101
20210102
20210103
20210104
20210105
;
Run;