topic Re: Base SAS Check a variable for a date value and then import file in SAS Programming

Base SAS Check a variable for a date value and then import file

Ivc — Fri, 26 Jul 2019 14:58:53 GMT

I have a Base SAS program that imports a 'CSV' file using the 'infile' statement without any issue. I ideally would like to check the variable 'dateto' to see if it matches today's date. If so, proceed to import the file if not stop the import. Below is my current code to import the 'csv' file. I would appreciate any help and I am not a SAS expert. Any simple code\process will be helpful.

I am using SAS version 9.4

/*creates a fileref for the external file*/
Filename ameritot '\\nyadpam\nyadpam_d\apps\CWA_Bondedge_INTMUTUALAMERI_TOT\Bondedge_upload_INTMUTUALAMERITOT.csv';
/* import the external file*/
data bondedge_upload_tot;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile ameritot delimiter=',' MISSOVER DSD LRECL=32767
FirstOBS=2;
informat Date_To $10. ;
informat CUSIP $14. ;
informat Market_Price $10. ;
informat Portfolio $21. ;
informat Orig_Face $17. ;
informat Actual_Par $18. ;
informat Book_Price $10. ;
informat Security_Type_Description $14. ;
format Date_To $10. ;
format CUSIP $14. ;
format Market_Price $10. ;
format Portfolio $21. ;
format Orig_Face $17. ;
format Actual_Par $18. ;
format Book_Price $10. ;
format Security_Type_Description $14. ;
input
Date_To $
CUSIP $
Market_Price $
Portfolio $
Orig_Face $
Actual_Par $
Book_Price $
Security_Type_Description $
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

run;

Thanks

Re: Base SAS Check a variable for a date value and then import file

PaigeMiller — Fri, 26 Jul 2019 15:02:12 GMT

@Ivc wrote:

I have a Base SAS program that imports a 'CSV' file using the 'infile' statement without any issue. I ideally would like to check the variable 'dateto' to see if it matches today's date. If so, proceed to import the file if not stop the import. Below is my current code to import the 'csv' file.

Seems to me this is circular logic. You can't know the value of DATETO until you import the file; but then you only want to import the file if DATETO is today's date, which you won't know until you import the file. Please explain further.

Re: Base SAS Check a variable for a date value and then import file

ballardw — Fri, 26 Jul 2019 15:12:00 GMT

You don't do yourself any favors by reading that variable as character

informat Date_To $10. ;

Since you don't show anything of what that may look like we can't even really suggest how to turn it into a date value to compare to "today".

As @PaigeMiller says, there is not going to be any way to avoid reading the file because the only place the date_to exists is in that file.

Read it. If the "date" doesn't match then you can delete the data set.

If your reason for not wanting to read the file is because it might replace an existing dataset when you don't want it to, then use a different dataset name on the read program, test the value from the data and if it doesn't match today then delete the data set or stop the program.

Re: Base SAS Check a variable for a date value and then import file

Reeza — Fri, 26 Jul 2019 15:12:55 GMT

Do you expect date_to to be constant throughout the file? If there are multiple values how should that be dealt with?

Re: Base SAS Check a variable for a date value and then import file

VDD — Fri, 26 Jul 2019 15:23:25 GMT

there should be a pattern to the file being updated. Is the task that needs to be ran monthly, daily, hourly? Please be kind to the network interruptions you impose on your companions? It sounds like a job scheduler task to me.

Re: Base SAS Check a variable for a date value and then import file

Ivc — Fri, 26 Jul 2019 18:11:06 GMT

Reeza,

The date will be constant throughout the file. The idea was to test the date in the file, if it fails then the file would not be imported. I guess I may have to go the route of importing and then check the resulting dataset.

Thanks

Re: Base SAS Check a variable for a date value and then import file

Reeza — Fri, 26 Jul 2019 18:52:33 GMT

Is the date in the filename or anywhere else? you can just read the first observation though, you don't need to read all, then if it matches go read all conditionally. It likely needs macro logic, are you familiar with that?

Re: Base SAS Check a variable for a date value and then import file

Ivc — Fri, 26 Jul 2019 19:01:44 GMT

Reeza,

I have not develop the macro skill.

Thanks

Re: Base SAS Check a variable for a date value and then import file

Reeza — Fri, 26 Jul 2019 19:05:25 GMT

What exact version of SAS are you using right now? The later versions have some different rules which affects how this needs to be coded. For example are you on 9.4 M5?

You can check it by using the following and the information is in the log.

proc product_status;run;

Re: Base SAS Check a variable for a date value and then import file

Ivc — Fri, 26 Jul 2019 19:09:15 GMT

Reeza,

Here is the version info:

For Base SAS Software ...

Custom version information: 9.4_M2

Image version information: 9.04.01M2P072314

For SAS/ACCESS Interface to ODBC ...

Custom version information: 9.4_M2

Thanks

Re: Base SAS Check a variable for a date value and then import file

Reeza — Fri, 26 Jul 2019 19:51:07 GMT

1. change the first step to read just the date, because it's the first variable this is doable and easy. Also set the obs=2 to read only the first one or two records. I can't recall the interaction between firstobs and OBS so setting it to 2 is safer. (Orange below)

2. Read the date in as a SAS date, not a character so you can test it correctly. (Purple)

3. Create a macro variable from the date to use in the next step (Blue)

4. Test that the macro variable is created (Pink)

5. Macro to conditionally read in data (Red)

6. Execute macro (Green)

HTH, if not, post back exactly the code you ran and the errors you're getting. Untested because of time and no data.

Filename ameritot '\\nyadpam\nyadpam_d\apps\CWA_Bondedge_INTMUTUALAMERI_TOT\Bondedge_upload_INTMUTUALAMERITOT.csv';
/* import the external file*/
data _null_;

infile ameritot delimiter=',' MISSOVER DSD LRECL=32767 FirstOBS=2 OBS=2;
informat Date_To yymmdd10. ;
format Date_To yymmdd10. ;


input Date_To $
;

call symputx("dateFromFile", date_to);

run;

*test macro variable is created and has a value. Will look like a number in the log;
%put &dateFromFile.;

%macro readFile(date_to = );
%if &date_to = %sysfunc(today()) %then %do;

***********************
*Import code goes here*
***********************;


%end;
%mend;

%readFile(date_to = &dateFromFile.);

@Ivc wrote:

Reeza,

Here is the version info:

For Base SAS Software ...

Custom version information: 9.4_M2

Image version information: 9.04.01M2P072314

For SAS/ACCESS Interface to ODBC ...

Custom version information: 9.4_M2

Thanks

Re: Base SAS Check a variable for a date value and then import file

Tom — Fri, 26 Jul 2019 19:43:25 GMT

That is one ugly data step. Looks like it was generated by PROC IMPORT.

You can use the optional CANCEL option on the RUN statement prevent the data step from running. That way you can control wheter it runs without having to learn how to generate code conditionally.

Just read enough of the file to check it you want to really read it or not and set a macro variable. Might be best to make the default be to NOT run the step and only run it when the file pases the test. Then an empty file will not try to be read.

filename ameritot '\\nyadpam\nyadpam_d\apps\CWA_Bondedge_INTMUTUALAMERI_TOT\Bondedge_upload_INTMUTUALAMERITOT.csv';

%let run_cancel=CANCEL ;
data _null_;
  infile ameritot firstobs=2 obs=3 dsd ;
  input date_to :$10. ;
  if input(date_to,??anydtdte.) = today() then call symputx('run_cancel',' ');
run;

data bondedge_upload_tot;
  infile ameritot dsd truncover firstobs=2 ;
  length Date_To $10 CUSIP $14 Market_Price $10 Portfolio $21
         Orig_Face $17 Actual_Par $18 Book_Price $10 Security_Type_Description $14
  ;
  input Date_To -- Security_Type_Description;
run &run_cancel ;

Re: Base SAS Check a variable for a date value and then import file

Reeza — Fri, 26 Jul 2019 19:39:42 GMT

The CANCEL option is a nifty little trick, still learning new things 🙂

Re: Base SAS Check a variable for a date value and then import file

Ivc — Fri, 26 Jul 2019 19:42:44 GMT

Reeza,

I am attaching the log and the program I ran.

Re: Base SAS Check a variable for a date value and then import file

Ivc — Fri, 26 Jul 2019 19:44:35 GMT

Here is the log

Re: Base SAS Check a variable for a date value and then import file

Tom — Fri, 26 Jul 2019 19:45:02 GMT

SAS logs (and programs) are just text, no need to attach them. Just use the Insert Code button (looks like {i}) on the menu bar to get a pop-up window where you can paste the lines of text.

Re: Base SAS Check a variable for a date value and then import file

Tom — Fri, 26 Jul 2019 19:46:31 GMT

You appear to be trying to reference a value you have not read yet.

641  call symputx("dateFromFile", date_to);
642
643  input Date_To $
644  ;

Re: Base SAS Check a variable for a date value and then import file

Reeza — Fri, 26 Jul 2019 19:52:08 GMT

Check the revised code in previous post - I cannot open attachment so going off what Tom posted.

Calculations can go after INPUT but need to be before DATALINES, which was my mistake.

Re: Base SAS Check a variable for a date value and then import file

Ivc — Mon, 29 Jul 2019 19:43:18 GMT

Reeza,

I would like to be able put a -1 or so where I can check a date less than today:

%if &date_to = %sysfunc(today()) %then %do;

Re: Base SAS Check a variable for a date value and then import file

Reeza — Mon, 29 Jul 2019 19:50:02 GMT

%if &date_to = %eval(%sysfunc(today()) - 1) %then %do;

Wrap it in %EVAL() to tell SAS to evaluate the value. You can also use INTNX() but in this case subtracting one is easy enough.

@Ivc wrote:

Reeza,

I would like to be able put a -1 or so where I can check a date less than today:
%if &date_to = %sysfunc(today()) %then %do;