topic Re: Import csvusing macro in SAS Programming

Import csv using macro-based filename

SAS_PA1 — Wed, 14 Mar 2018 15:54:42 GMT

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.

Re: Import csvusing macro

ballardw — Sat, 10 Mar 2018 00:10:53 GMT

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.

Re: Import csvusing macro

SAS_PA1 — Sat, 10 Mar 2018 06:03:47 GMT

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"

Re: Import csvusing macro

ballardw — Mon, 12 Mar 2018 15:21:53 GMT

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

Re: Import csvusing macro

SAS_PA1 — Tue, 13 Mar 2018 01:12:04 GMT

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"

Re: Import csvusing macro

SAS_PA1 — Wed, 14 Mar 2018 00:35:48 GMT

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"

Re: Import csvusing macro

Tom — Wed, 14 Mar 2018 03:54:56 GMT

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

Re: Import csvusing macro

SAS_PA1 — Wed, 14 Mar 2018 15:41:38 GMT

Actual Name of the file

der_extract_monthly_decisiondate_030418_20180303235454.csv

As this is the monthly file datetime cahnge every month.

Re: Import csvusing macro

SAS_PA1 — Tue, 20 Mar 2018 22:28:09 GMT

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

Re: Import csvusing macro

SAS_PA1 — Wed, 21 Mar 2018 00:23:06 GMT

%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