topic IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT in SAS Programming

IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Ronein — Thu, 03 Jun 2021 17:24:08 GMT

Hello

Let's talk about the following activity.

1-My manager send me list of dates

2-There are CSV files with following name structure RevenueYYYYMMDD (for example:Revenue20210603).

3-The task is to import the CSV files into SAS data set only for specific dates

4-I want to import CSV files if they exists

If a file doesn't exits then I want to create an empty data set (data set without rows).

I want to ask 2 questions please:

1-What statements should be added in order to tell SAS to create an empty data set if it doesn't exist?

(In the current code if the CSV files doesnt exist then SAS data set is not created)

2-In the real life I have many dates (1000 dates ) and I am not sure if it is good solution to create a macro varaible called vector . What is the way to perform conditional import of CSV files only for dates that exists in dataset?(In this example data set datestbl define the dates to import)

Data datestbl;
input dates $;
cards;
20210603
20210601
20210528
20210514
;
Run;

PROC SQL noprint;
	select distinct   dates 
         into: vector
	separated by '+'
	from  datestbl
;
QUIT;
%put &vector.;
%let count = %sysfunc(countw(&vector));
%put &count;
 


%macro RRR; 
%do j=1 %to &count.;
%let YYYYMMDD=%scan(&vector.,&j.,+);

%if %sysfunc(fileexist(/path/Revenue&YYYYMMDD.))
%then %do;
options validvarname=v7;
Filename ttt "/path/Revenue&YYYYMMDD.";
proc import datafile=ttt out=Revenue&YYYYMMDD.  dbms=dlm replace ;
getnames=yes;
datarow=2;
delimiter=';'
;
run;
%end;
%end;
%mend RRR;
%RRR;

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Reeza — Thu, 03 Jun 2021 17:32:22 GMT

Look up CALL EXECUTE instead of macro loops, much easier to debug and work with.

Checks if a file exists (FILEEXISTS() function)
1. If it exists, import the file
2. If it doesn't, generate empty file
Wrap program from #1 into a macro
Use a data step to call macro from CALL EXECUTE passing the list of dates

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Kurt_Bremser — Thu, 03 Jun 2021 17:39:52 GMT

First of all, do not use PROC IMPORT for CSV files, especially not if you need to combine/compare them later.

Write a macro that accepts a datestring as argument:

%macro import_one(date);
%if %sysfunc(fileexist(path/revenue&date..csv))
%then %do;
data revenue&date.;
infile "/path/revenue&date..csv))" dlm=',' dsd truncover;
length /* insert variable definitions here */;
input /* variable list as above */;
run;
%end;
%else %do;
data revenue&date.;
length /* same as above */;
run;
%end;
%mend;

then call this from the data:

Data datestbl;
input dates $;
call execute('%nrstr(%import_one('!!date!!'))');
cards;
20210603
20210601
20210528
20210514
;

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Tom — Thu, 03 Jun 2021 17:40:36 GMT

Do you have to use PROC IMPORT? Do you not know what variables are in the CSV files?

It is pretty simple to just read all of the files in one data step instead of making multiple datasets.

Just replace the part in inner most DO loop with the right INPUT statement for how your CSV files are defined.

data datestbl;
  input dates $;
cards;
20210603
20210601
20210528
20210514
;

data revenue ;
  set datestbl;
  length filename $200;
  filename = cats('/path/Revenue',dates,'.csv');
  if fileexist(filename) then do;
    infile csv dsd dlm=';' truncover firstobs=2 end=eof filevar=filename;
    do while(not eof);
       input .... ;
       output;
    end;
  end;
  else do;
     put filename= 'does not exist';
  end;
run;

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Ronein — Thu, 03 Jun 2021 18:10:45 GMT

I am not sure if it CSV.
This code import the file correctly and there are 59 columns called:Var1.Var2,...Var59.

%let YYYYMMDD=20210601;
proc import datafile="/path/Revenue&YYYYMMDD."
out=tbl&YYYYMMDD. dbms=dlm replace ;
getnames=no;
datarow=2;
delimiter=';'
;
run;

However,this code is not working well and a data set called Revnue was created with one column called dates with 0 rows
data datestbl;
input dates $;
cards;
20210601
;
data revenue ;
set datestbl;
length filename $200;
filename = cats('/path/Revenue',dates,'.csv');
if fileexist(filename) then do;
infile csv dsd dlm=';' truncover firstobs=2 end=eof filevar=filename;
do while(not eof);
output;
end;
end;
else do;
put filename= 'does not exist';
end;
run;

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Tom — Thu, 03 Jun 2021 18:15:40 GMT

You did not tell it read any variables from the CSV file.

A CSV file is not some mythical creature from the dark lagoon. It is just a text file. Open the text file and look at it. If you don't have any documentation from the source on what the variables are they should be pretty clear from the column headers (first row) and the types of values they contain.

If you want to read 59 character variables with maximum length of 20 bytes each your inner DO loop could be:

do while(not eof);
  length var1-var59 $20 ;
  input var1-var59 ;
  output;
end;

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Ronein — Thu, 03 Jun 2021 18:30:04 GMT

Okay ,now it is working well but there is another problem.

The name of the data set that I create should include the date on it (for example:Revenue20210601)

What is the way to add date to data set name here?

data datestbl;
  input dates $;
cards;
20210601
20210602
20210603
;
Run;
data cats(revenue,dates) ;
set datestbl;
length filename $200;
filename = cats("/path/Revenue",dates);
if fileexist(filename) then do;
    infile csv dsd dlm=';' truncover firstobs=2 end=eof filevar=filename;
do while(not eof);
  input var1-var59 ;
  output;
end;
  end;
  else do;
     put filename= 'does not exist';
  end;
run;

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Ronein — Thu, 03 Jun 2021 18:44:28 GMT

The name of the data set should contain the date.
In this example there are potential 4 data sets to create but you gave one name "revenue"

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Tom — Thu, 03 Jun 2021 19:20:42 GMT

Why would you want to make a separate dataset for each date?

If you want to only look at some of the data for a particular analysis just use a WHERE statement.

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Ronein — Fri, 04 Jun 2021 15:47:05 GMT

Thank you

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Ronein — Fri, 04 Jun 2021 15:51:26 GMT

Thank you, May you show the full code please of import multiple CSV files in your approach?

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Tom — Fri, 04 Jun 2021 15:59:43 GMT

Show me the what columns are in your CSV files and how you want them defined as variables in the resulting SAS dataset.

For example you might have data like:

Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98

So you would want to define the variables like and read them in using code like:

length name $7 sex $1 age height weight 8 ;
input name -- weight;

Re: IMPORT IF EXISTS AND CREATE EMPTY DATASET IF NOT

Kurt_Bremser — Fri, 04 Jun 2021 16:09:56 GMT

The method to import multiple files into one dataset in one data step is shown here.