topic Re: Failure to import csv data using fileexist in SAS Programming

Failure to import csv data using fileexist

mmaleta851 — Tue, 13 Sep 2022 18:56:42 GMT

So I am working a project in SAS Enterprise Guide that pull daily data from a server, imports a csv with monthly data, appends the daily data to the monthly data, and re-saves the monthly csv to be used for analysis and to be pulled again the following day. The program I am having issues with is the program that imports the monthly CSV. The program is supposed to work as follows:

%let path=C:\\;



%macro import_date;
%if %sysfunc(fileexist(&path\Historical %sysfunc(intnx(day, %sysfunc(today()), -1, s), monyy7.)  .csv))
%then %do;
data work.import;
	infile "&path\Historical %sysfunc(intnx(day, %sysfunc(today()), -1, s), monyy7.)  .csv" dsd firstobs=2;
	informat PlayerId 8. FirstName LastName 'Market (Marketing)'n $40.  Date Date9. Sales;
	format PlayerId 8. FirstName LastName 'Market (Marketing)'n Date Date9. Sales;
	input PlayerId FirstName $ LastName $ 'Market (Marketing)'n  $ Date Sales;
run;
%end;
%else %do;
data work.import;
run;
%end;
%mend;

It checks to see if the the monthly csv file exists in the path. If it does it is supposed to pull that file in order to be appended to the daily data set. If not, it creates a blank data set to append to. The program does run, but when the end result says "'work.import' does not exist. You will need to refresh the task that created it before it can be opened." However, when I isolate the data step and run it, and then re-run the entire program, it pulls the csv file. Any idea of what I am doing wrong?

Re: Failure to import csv data using fileexist

ballardw — Tue, 13 Sep 2022 19:58:47 GMT

First thing would be to check what the actual file name is that you pass to the Fileexist function.

For example when I extract just the part of the code that creates the name:

%let path=C:\\;

%let result =&path\Historical %sysfunc(intnx(day, %sysfunc(today()), -1, s), monyy7.)  .csv;

I get

4    %put result is:&result;
result is:C:\\\Historical SEP2022  .csv

I question your definition of Path with 2 slashes to begin with but you add another in the file name.

The space between "Historical" and %sysfunc means that there is a space in the resulting name. Again the spaces before the ".csv" means there are two spaces in the file name before .csv;

So if the filename does not resolve correctly then the Fileexist returns 0 and is false, so nothing happens.

When a MACRO misbehaves try running the code with OPTIONS MPRINT SYMBOLGEN; to see details of the generated code in the log. Symbolgen will provide a trace of how macro variables resolve to create a result.

Re: Failure to import csv data using fileexist

Tom — Tue, 13 Sep 2022 19:42:42 GMT

It might help if the data step did not have conflicting definitions for some of the variables.

You force SAS to guess that FIRSTNAME and LASTNAME should be character variables and guess that the length should be 40 bytes. But then you try to attach the numeric format DATE to the variables.

It also might help to define the filename in just one place, to avoid possible inconsistency.

%let fname=&path\Historical %sysfunc(intnx(day, %sysfunc(today()), -1, s), monyy7.)  .csv;

data work.import;
	length PlayerId 8 FirstName LastName 'Market (Marketing)'n $40 Date Sales 8;
	format Date Date9.;
%if %sysfunc(fileexist(&fname)) %then %do;
	infile "&fname" dsd firstobs=2;
	input PlayerId FirstName LastName 'Market (Marketing)'n  Date :date. Sales;
%end;
%else %do;
    stop;
%end;
run;

Dose the filename actually have all of those spaces before the .csv ?

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 18:10:40 GMT

The %let path=C\\; is just a placeholder for the file path I use not my actual file path..

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 18:16:59 GMT

That solution wouldn't work for me. Whenever there is a new month I need to create a blank dataset for the new month so I can add the data for the first day of the month to that dataset and have that be the dataset. The point of the project is to accumulate daily data into monthly groups so it can be used externally for analysis.

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 18:30:07 GMT

Additionally, DATE is not a numeric but date at which each instance occurred.

Re: Failure to import csv data using fileexist

Tom — Wed, 14 Sep 2022 18:42:31 GMT

@mmaleta851 wrote:

Additionally, DATE is not a numeric but date at which each instance occurred.

I have not the slightest idea what you are talking about.

If the DATE variable is not a number then why did you attach the numeric DATE9. format specification to it in the data step?

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 18:58:30 GMT

Sorry I meant to write the DATE is not a numeric format that I am applying to the FirstName or LastName variable but a variable of itself that I am applying the Date9. format to.

Re: Failure to import csv data using fileexist

Tom — Wed, 14 Sep 2022 19:05:04 GMT

@mmaleta851 wrote:
Sorry I meant to write the DATE is not a numeric format that I am applying to the FirstName or LastName variable but a variable of itself that I am applying the Date9. format to.

But this is NOT what the posted code was doing.

The code looked something like this:

format A B date9.;

Which means attach the DATE9. format specification to all of the variables listed before it, so to both A and B.

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 19:09:53 GMT

I see what the issue is. I forgot to put $40 in between 'Market (Marketing)'n and Date in the format step. I apologize.

Still even including this, the file does not export.

Re: Failure to import csv data using fileexist

Tom — Wed, 14 Sep 2022 19:14:42 GMT

@mmaleta851 wrote:
I see what the issue is. I forgot to put $40 in between 'Market (Marketing)'n and Date in the format step. I apologize.

Still even including this, the file does not export.

What do you mean EXPORT? There is no EXPORT in the code you posted. It was just a step to create a dataset that was named IMPORT. The only conditional part was whether or not it would do that by reading from a CSV file or not.

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 19:20:52 GMT

When I run the task, it runs but the resulting datafile says:

"WORK.IMPORT" does not exist. You will need to refresh the task that created it before it can be opened.

Re: Failure to import csv data using fileexist

Reeza — Wed, 14 Sep 2022 19:25:23 GMT

Add the options

option mprint symbolgen;

and post the log with the full error.

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 19:27:52 GMT

Hey Reeza,

I am using SAS EG. To run that option, do I include that in my code or do I use the options menu?

Re: Failure to import csv data using fileexist

Reeza — Wed, 14 Sep 2022 19:40:16 GMT

Include it in your code before the macro code and macro call.

Re: Failure to import csv data using fileexist

mmaleta851 — Wed, 14 Sep 2022 20:01:26 GMT

Gotcha.

I have included that before the code and got a log of SASWORKLOCATION but nothing else.

Re: Failure to import csv data using fileexist

Reeza — Wed, 14 Sep 2022 20:19:21 GMT

Did you call the macro as well? There's nothing in the code shown that actually calls the macro so you may need to add that in as well if it's somewhere else in the process.

%let path=C:\\;

options mprint symbolgen mlogic;

%macro import_date;
%if %sysfunc(fileexist(&path\Historical %sysfunc(intnx(day, %sysfunc(today()), -1, s), monyy7.)  .csv))
%then %do;
data work.import;
	infile "&path\Historical %sysfunc(intnx(day, %sysfunc(today()), -1, s), monyy7.)  .csv" dsd firstobs=2;
	informat PlayerId 8. FirstName LastName 'Market (Marketing)'n $40.  Date Date9. Sales;
	format PlayerId 8. FirstName LastName 'Market (Marketing)'n Date Date9. Sales;
	input PlayerId FirstName $ LastName $ 'Market (Marketing)'n  $ Date Sales;
run;
%end;
%else %do;
data work.import;
run;
%end;
%mend;

%import_date;

Re: Failure to import csv data using fileexist

ballardw — Wed, 14 Sep 2022 20:27:09 GMT

@mmaleta851 wrote:

The %let path=C\\; is just a placeholder for the file path I use not my actual file path..

Does not invalid the concerns about the spaces in the fileexist call for the file name. Have you indeed verified, somewhere, that the string you create matches an actual file name in the operating system.

Sometimes it makes sense to create a separate macro variable to hold the value. Especially since it is reused such as your INFILE statement. That way you only have to correct syntax issues, such as the spaces before .CSV in one place.

Next bit: Is that path relative the SAS system running the code? If the path starts at C:\ then it would be the C:\ of the server running SAS if you are running any server version.