DATA Step, Macro, Functions and more

Import csv using macro-based filename

Reply
Contributor
Posts: 21

Import csv using macro-based filename

[ Edited ]

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.

Super User
Posts: 13,023

Re: Import csvusing macro

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.

Contributor
Posts: 21

Re: Import csvusing macro

[ Edited ]

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"

Super User
Posts: 13,023

Re: Import csvusing macro


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

Contributor
Posts: 21

Re: Import csvusing macro

[ Edited ]

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"

 

Contributor
Posts: 21

Re: Import csvusing macro


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"

Super User
Super User
Posts: 7,847

Re: Import csvusing macro


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

 

 

Contributor
Posts: 21

Re: Import csvusing macro

Actual Name of the file  

der_extract_monthly_decisiondate_030418_20180303235454.csv

 

As this is the monthly file datetime cahnge every month.

Contributor
Posts: 21

Re: Import csvusing macro

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

Contributor
Posts: 21

Re: Import csvusing macro

%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
Ask a Question
Discussion stats
  • 9 replies
  • 154 views
  • 1 like
  • 3 in conversation