Failure to import csv data using fileexist

[mmaleta851]

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?

[ballardw]

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.

[mmaleta851]

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

[ballardw]

---

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

[Tom]

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 ?

[mmaleta851]

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.

[mmaleta851]

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

[Tom]

---

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

[mmaleta851]

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.

[Tom]

---

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

[mmaleta851]

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.

[Tom]

---

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

[mmaleta851]

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.

[Reeza]

Add the options 

option mprint symbolgen;

and post the log with the full error. 

[mmaleta851]

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?