BookmarkSubscribeRSS Feed
SAS_PA1
Calcite | Level 5

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.

9 REPLIES 9
ballardw
Super User

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.

SAS_PA1
Calcite | Level 5

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"

ballardw
Super User

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

SAS_PA1
Calcite | Level 5

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"

 

SAS_PA1
Calcite | Level 5


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"

Tom
Super User Tom
Super User

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


 

  • Do you know the ACTUAL name of the file you need to read?  Or do you just know the PATTERN of the file's name.
  • Is it that the 'der_extract_monthly_patsiondate_' part is always the same and the 6 digit and 14 digit number parts vary? 
    • Or is it the reverse?  That you know the digits and want to search existing files that have those digits in the name?
  • Is the 6 digit part supposed to represent a date?  If so do you know the date? But perhaps not know the exact 14 digit part of te filename?
  • Do you have multiple files with this same pattern of filenames in the same directory?  If so then how do you know which one you want to read?

You need to explain in either other words or provide more repetitions of the pattern to help us understand.

 

 

SAS_PA1
Calcite | Level 5

Actual Name of the file  

der_extract_monthly_decisiondate_030418_20180303235454.csv

 

As this is the monthly file datetime cahnge every month.

SAS_PA1
Calcite | Level 5

der_extract_monthly_decisiondate_030418_20180303235454.csv yes 6 digit and 14 digit part vary and file name remains the same. Thank you.

SAS_PA1
Calcite | Level 5
%lET FNAME = %SYSFUNC(CATS(DER_EXTRACT_MONTHLY_DECSIONDATE_,%SYSFUNC(TODAY(), MMDDYY6. ),_,%SYSFUNC(YEAR("&SYSDATE"D)),%SYSFUNC(MONTH("&SYSDATE"D)),%SYSFUNC(DAY("&SYSDATE"D)),%SYSFUNC(TIME(), HHMMSS6.),.CSV));
%PUT &FNAME.; i WAS TRYING TO GET THIS FINALLY RESOLVED IT, Thank you every one

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1701 views
  • 1 like
  • 3 in conversation