I have to read this actual file der_extract_monthly_patsiondate_030418_20180303235454.csv and the should generated as der_extract_monthly_patsiondate.csv date and time may vary every month.
%let fname =der_extract_monthly_patsiondate_%sysfunc(date(030418_20180303235454).csv;
%put &fname;
Filename fileref FTP "/ghms/Appeals/ Reporting/Automated/Monthly/Monthly_hmp_Activity/File /aging/Reports/&filename."
final File should be generated as der_extract_monthly_patsiondate.csv please suggest the changes. Thank you.
It isn't quite clear what your issue is.
If you are trying to build a file name statement then either we need a rule for finding the part that I think you highlighted, which I think isn't likely from your description.
Is this supposed to be the latest date attached to the file name in that folder?
Note that you received errors when attempting to execute
%let fname =der_extract_monthly_patsiondate_%sysfunc(date(030418_20180303235454).csv;
Did you try to address those?
First you are missing a )
%let fname =der_extract_monthly_patsiondate_%sysfunc(date(030418_20180303235454)).csv;
But that still generates an error because the function DATE is used as Date() , no parameters, and returns the current date.
You should run this code to see what kind of values DATE would return to a macro variable:
%let fname =der_extract_monthly_patsiondate_%sysfunc(date(030418_20180303235454).csv;
If you are looking for a files name to use in the FILENAME statement we need a rule to generate those parts.
And since the location is on an FTP site that is going to reduce the typical use of an OS command to get a listing of files in the source folder.
You may be able to get the list of files if you know how to issue and FTP Command(or sequence) to generate a list of files at that location.
Also please clarify what "File should be generated as der_extract_monthly_patsiondate.csv" may mean. I would expect to use the filename to create a SAS data set since you start off with "I have to read..". So it does not sound like SAS should be generating any CSV file at all.
At given path it generates monthly report, I need to import the report datetime may vary every month I have to automate this, file should be generatef as der_extract_monthly_patsiondate.csv"
@SAS_PA1 wrote:
At given path it generates monthly report, I need to import the report datetime may vary every month I have to automate this, file should be generatef as der_extract_monthly_patsiondate.csv"
Do you need to import the CSV file or to read the date and time a file was created?
CAN you provide ANY rule about the name of the file?
If not then you may have to use either an operating system command to get the names of the files in your source folder and the creation date and find the last one. IF that is the file you want.
You can pipe the result of your OS listing results to a SAS FILENAME
filename dirlist PIPE "DIR d:\data\*.csv" ; data junk; infile dirlist; length line $100.; input; line = _infile_; run;
on a windows system will create a dataset junk with the contents of a directory listing, one line of text per line of result. Options on the DIR command would change the resulting input contents. The result can be parsed to find the file with the latest creation date.
Then you could use that to create an macro variable for use in an infile statement to read the specific csv file into SAS.
"I have to automate this, file should be generatef as der_extract_monthly_patsiondate.csv" makes no sense in terms of SAS.
This may change every month how to hardcode this
%sysfunc(date(030418_20180303235454)
SourceFile1 = "der_extract_monthly_decisiondate_" & Convert_Date(Run_Date) & "*" & ".csv"
NewNameFile1 = "der_extract_monthly_decisiondate.csv"
I have to read this actual file der_extract_monthly_decisiondate_030418_20180303235454.csv and the should generated as der_extract_monthly_patsiondate.csv date and time may vary every month.
Import der_extract_monthly_decisiondate_030418_20180303235454.csv file with Date and generate it as der_extract_monthly_decisiondate.csv
%let fname =der_extract_monthly_patsiondate_%sysfunc(date(030418_20180303235454).csv
This may change every month how to hardcode this (date(030418_20180303235454)
%sysfunc(date(030418_20180303235454)SourceFile1 = "der_extract_monthly_decisiondate_" & Convert_Date(Run_Date) & "*" & ".csv"
SourceFile1 = "der_extract_monthly_decisiondate_" & Convert_Date(Run_Date) & "*" & ".csv"
NewNameFile1 = "der_extract_monthly_decisiondate.csv"
@SAS_PA1 wrote:
I have to read this actual file der_extract_monthly_decisiondate_030418_20180303235454.csv and the should generated as der_extract_monthly_patsiondate.csv date and time may vary every month.
Import der_extract_monthly_decisiondate_030418_20180303235454.csv file with Date and generate it as der_extract_monthly_decisiondate.csv
%let fname =der_extract_monthly_patsiondate_%sysfunc(date(030418_20180303235454).csv
This may change every month how to hardcode this (date(030418_20180303235454)
%sysfunc(date(030418_20180303235454)SourceFile1 = "der_extract_monthly_decisiondate_" & Convert_Date(Run_Date) & "*" & ".csv"
SourceFile1 = "der_extract_monthly_decisiondate_" & Convert_Date(Run_Date) & "*" & ".csv"
NewNameFile1 = "der_extract_monthly_decisiondate.csv"
You need to explain in either other words or provide more repetitions of the pattern to help us understand.
Actual Name of the file
der_extract_monthly_decisiondate_030418_20180303235454.csv
As this is the monthly file datetime cahnge every month.
der_extract_monthly_decisiondate_030418_20180303235454.csv yes 6 digit and 14 digit part vary and file name remains the same. Thank you.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.