topic using proc contents variable in SAS Enterprise Guide

using proc contents variable

jimksas — Thu, 17 Apr 2014 00:16:41 GMT

Hello Friends,

i am using below code and once i got "work.date" after end of the proc contents step, i want to use "create date" and "last modified date" variable in %macro step for multiple if statement.

Can someone please tell me how i can use those two variable in next %macro step?

----------------------------------------------------------------

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';

data work.use_date;

infile filelist truncover lrecl=256;

input filename $256.;

name=scan(filename,1,'_');

run;

proc sort data=work.use_date;

by name;

run;

proc contents data=work.use_date out=work.date;

run;

------------------------------------------------------------------------------------------

/*i want to use "create date" and "last modified date" from work.date here */

%macro xyz;

%if create_date=today() %then %do;

data....

.....

....

run;

%else %do;

data....

....

...

run;

data....

....

...

run;

data....

....

...

run;

%end;

%mend;

%xyz;

Re: using proc contents variable

TomKari — Thu, 17 Apr 2014 01:22:16 GMT

I'm not sure that your code is meeting your needs.

The "create date" and "last modified date" variables in "work.date" are the date that work.use_date are created, which is the date that you are running the project.

What are you trying to do with this code?

Tom

Re: using proc contents variable

jimksas — Thu, 17 Apr 2014 02:44:11 GMT

I want to use those two variable like this;

%macro check_new_date;

%if datepart(create_date) = today() %then

%do;

data _null_;

abort return;

run;

%else %do;

data check1;

.....

run;

proc sort data=check1;

by....

run;

data check2;

....

run;

proc sort data=check2;

by ...

run;

data check;

merge check1 (in=a) check2 (in=b)

if a=1;

run;

and more data setps;

run;

%end;

%mend;

%check_new_date;

Re: using proc contents variable

SKK — Thu, 17 Apr 2014 06:18:32 GMT

Maybe this will help you:

proc sql noprint;

select distinct datepart(crdate), datepart(modate) into :create_date, :modf_date from work.date;

quit;

%macro check_new_date;
%if &create_date = %sysfunc(today()) %then

%do;

.........

Re: using proc contents variable

RW9 — Thu, 17 Apr 2014 09:10:03 GMT

I am afraid that TomKari is correct here. Your logic doesn't make sense as is. The date created and date modified will be the date time that this part of the code is run:

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';

data work.use_date;

Now what I think you want is the date time of the file as it is from the operating system. I.e. if I create a file in Windows on the 13MAR2013, then I run your code on the 26JAN2014, the creation date would be 26JAN2014, whereas what you want to know is 13MAR2013. To do this you need to modify how you get the directory listing. Doing the below gets the directory listing, then post processes the info to get creation date time, size and filename. These can then be symputted into macro variables or otherwise processed. Note the /tc in the pipe, this means the date/time is the creation date/time. If you want accessed then /ta, for last written use /tw.

filename mypipe pipe 'dir "s:\temp\rob" /tc';

data use_date;
attrib buffer format=$2000.
              file_last_written_date format=date9.
              file_last_written_time format=time5.
              file_size format=best.
              file_name format=$200.;
infile mypipe;
input buffer $2000.;
if substr(buffer,1,5) in ("Volum","Direc") or index(buffer,"<DIR>")>0 or index(buffer,"Dir(s)")>0 then delete; /* Remove some extra info */
file_last_written_date=input(put(scan(compbl(buffer),1,' '),$10.),ddmmyy10.); /* note dependant on system settings */
file_last_written_time=input(put(scan(compbl(buffer),2,' '),$5.),time5.);
file_size=scan(compbl(buffer),3,' ');
file_name=scan(compbl(buffer),4,' ');
run;

Re: using proc contents variable

ballardw — Thu, 17 Apr 2014 15:31:15 GMT

What does Proc contents have to do with this question?

Re: using proc contents variable

jimksas — Thu, 17 Apr 2014 16:00:42 GMT

Hi Ballardw - goal is to use "create date" and or "last modified date" from "proc contents" for further "if then else" condition....

Re: using proc contents variable

jimksas — Thu, 17 Apr 2014 18:35:11 GMT

I am sorry - I am trying to get "created date" for .mdb files exist on that folder ""f\shares\jim\finance\*.mdb"...

and then based on that (created date) - my next logic will be...

How can i get it...please?

Thanks.....

Re: using proc contents variable

cwilson — Thu, 17 Apr 2014 19:10:52 GMT

I see about 4 things that need to be done. I do something similar where I use Proc Contents to check a variable list of incoming Access files, to look for specific tables, and then load data from those tables.

1. Get Create_date from Proc Contents on the Access data file

2. You need to add parameters to your Macro for the filename and the creation date to pass into the Macro

3. Because your macro is doing full DATA steps, you can't call the macro from inside a DATA step. You need to have what I call a "driver" macro, to loop through the list of files.

4. In preparation for the driver macro, I use CALL SYMPUT to write each filename to a macro variable with a counter for the suffix, and loop through those in the driver macro.

I have program code, but I would need to first edit it heavily. I have been swamped this morning, so I have not yet done that.

Re: using proc contents variable

jimksas — Thu, 17 Apr 2014 19:16:56 GMT

thanks CWILSON - i am using "proc contents" to get date information, and SK K solutions for macro variable (you are saying call symput)...i think it will work...but if you share code with your logic - would be grt...

thanks!

Re: using proc contents variable

cwilson — Sat, 19 Apr 2014 15:36:21 GMT

There are a couple things that are not clear to me:

1. When you get your file list, I assume you are expecting multiple files, right?

2. I don't understand why you scan up to the first "_" when parsing the file name, because to process the file, you will want the full file name.

3. In your second post, you indicate that if the file being processed was created today, then you want to abort return? Does that mean you want to stop all processing? Or you just don't want to process that particular file? (I normally would expect to move on to the next file, so I would change the %if logic slightly.)

Caveats:

1. I have not tested this code.

2. I have never tried to get the create date for an Access file using the output from the Proc Contents. In order to get the list of tables inside the Access file, I had to use ODS trace on and ODS output statemtns, which I have excluded from the code below, in the hopes that the Proc Contents output gives you what you want.

3. If nothing else, this should move you forward to what you want.

Starting with your code:

----------------------------------------------------------------

filename filelist pipe 'dir "f:\shares\jim\finance\*.mdb" /b';

data work.use_date;

infile filelist truncover lrecl=256;

input filename $256.;

name=scan(filename,1,'_');

run;

proc sort data=work.use_date;

by name;

run;

/* this step won't give you what you want...

proc contents data=work.use_date out=work.date;

run;

* read set of filenames that are now sorted and generate macro variables ;

data work.use_date;

set work.use_date end=EOF ;

CALL SYMPUT('file' || trim(left(_n_)), trim(filename) ) ; ** correction ;

if EOF then CALL SYMPUT('filecount', _n_) ;

run;

* this will give you macros file1, file2, etc. ;

%put file count = &filecount ;

** NEW: Driver macro that will loop through list of files ;

%macro LoopFiles ;

%if &filecount > 0 %then %do ;

%do i = 1 %to &filecount ; ** correction ;

%check_new_date(&&file&i); *notice recursive macro syntax - resolve &i then resolve &file1, etc..;

%end ;

%mend LoopFiles ;

------------------------------------------------------------------------------------------

** add file parameter to your macro ;

%macro check_new_date(thisfile);

* check the date of the Access datafile inside your macro (combination of SKK and my Access file code) ;

%LET accfile1="f\shares\jim\finance\&thisfile" ;

LIBNAME thisacc ="f:\shares\jim\finance\&thisfile"; ** correction ;

proc contents data=thisacc._all_ out=work.date; ** or replace _all_ with a specific table name, if you know what it is ;

run;

** check values in work.date ;

proc print data = work.date ;

var libname memname crdate modate ;

run ;

proc sql noprint;

select distinct datepart(crdate) into :create_date from work.date;

quit;

%PUT Create_date for &thisfile = &create_date ;

%if &create_date = %sysfunc(today()) %then

%do;

data _null_;

abort return;

run;

%else %do;

data check1;

.....

run;

proc sort data=check1;

by....

run;

data check2;

....

run;

proc sort data=check2;

by ...

run;

data check;

merge check1 (in=a) check2 (in=b)

if a=1;

run;

and more data setps;

run;

%end;

%mend check_new_date;

** call driver macro ;

%LoopFiles;

Message was edited by: Carla Wilson A few corrections to my program code, flagged with ** correction ;

Re: using proc contents variable

jimksas — Tue, 22 Apr 2014 23:38:54 GMT

I am thinking to use this rather...almost similar i guess...

options mprint mlogic symbolgen;

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';

data work.use_date;

infile filelist truncover lrecl=256;

input filename $256.;

name=scan (filename,1,'_');

run;

proc sort data=work.date;

by name;

run;

proc contents data=work.use_date out=work.chk_date;

run;

proc sql;

select distinct datepart(crdate), datepart(modate) into :create_date, :modf_date

from work.chk_date;

quit;

%put &create_date;

%put &modf_date;

%if &create_date = %sysfunc(today()) %then

%do;

Re: using proc contents variable

jimksas — Wed, 23 Apr 2014 20:14:29 GMT

even after all trials i am not getting what i want...I agree with TomKari's statement...

"The "create date" and "last modified date" variables in "work.date" are the date that work.use_date are created, which is the date that you are running the project."

No i don't know how i will get that...created date on existing file...(let's say that it's not today or day when i want to run code...

Please help...!!!

Re: using proc contents variable

cwilson — Wed, 23 Apr 2014 20:32:29 GMT

Yes, I also indicated in the code I posted that the proc contents on work.use_date was not going to work.

I did have some questions for you at the top of my post. Please reply.

Furthermore, I don't believe you can use %if macro code in open SAS code. I think it needs to be in a defined macro. (but I could be wrong.)

I will have to set up a test to test the ability to read the create_date from an Access file, and this will have to be after-hours.

Re: using proc contents variable

jimksas — Wed, 23 Apr 2014 20:47:31 GMT

I am embedding everything under macro only, so let's say we can go with this i guess...big question for me is, how to use "create_date" variable ?

%macro jimk;

options mprint mlogic symbolgen;

filename filelist pipe 'dir "f\shares\jim\finance\*.mdb" /b';

data work.use_date;

infile filelist truncover lrecl=256;

input filename $256.;

name=scan (filename,1,'_');

run;

proc sort data=work.date;

by name;

run;

proc contents data=work.use_date out=work.chk_date;

run;

proc sql;

select distinct datepart(crdate), datepart(modate) into :create_date, :modf_date

from work.chk_date;

quit;

%put &create_date;

%put &modf_date;

%if &create_date = %sysfunc(today()) %then

%do;

....

...

%mend;

jimk;

%jimk;

Re: using proc contents variable

cwilson — Thu, 24 Apr 2014 02:26:43 GMT

Ok, I set up a test folder with some test Access datafiles and executed my program. I found a couple mistakes in my code, which I corrected in my post above. The program "worked" to a point. It did loop throught the files very nicely, but the creation date was always missing. I put a Proc Print after the Proc Contents step, and it turns out that crdate and modate, although available in the Proc contents output, are always missing. Here is an example of the results:

The SAS System 18:58 Wednesday, April 23, 2014 58

Obs LIBNAME MEMNAME CRDATE MODATE

26 THISACC Failure List . .

27 THISACC Graph Examples . .

So, unfortunately, it looks like perhaps Proc Contents is not able to determine the crdate for an Access file.

So, I tried the code posted by RW9. I did have to tweak it a bit to successfully read my piped dir data (varies with system settings). I also found I needed to use the option /tw, instead of /tc to get the dates I wanted. /tc gave me the date that I copied the files into my test folder not the original file date.

filename mypipe pipe 'dir "C:\LocalData\AccessTest\*.mdb" /tw';

data use_date;
attrib buffer format=$2000.
              file_last_written_date format=date9.
              file_last_written_time format=time5.
              am_pm format=$2.
              file_size format=$30.
              file_name format=$50.;
infile mypipe truncover ;
input buffer $2000.;
if substr(buffer,1,5) in ("Volum","Direc") or index(buffer,"<DIR>")>0 or index(buffer,"Dir(s)")>0 or index(buffer,"File(s)")>0 then delete; /* Remove some extra info */
file_last_written_date=input(put(scan(compbl(buffer),1,' '),$10.),mmddyy10.); /* note dependant on system settings */
file_last_written_time=input(put(scan(compbl(buffer),2,' '),$5.),time5.);
am_pm=scan(compbl(buffer),3,' ');
file_size=scan(compbl(buffer),4,' ');
file_name=trim(scan(compbl(buffer),5,' ')) ;
if file_name = ' ' then delete ;
run ;

proc print data = use_date ;
var file_name file_last_written_date ;
run ;

So, I changed my CALL SYMPUT step to create a macro variable for the crdate as well as the file name, and then you would change the Driver macro and your own Macro program to have two parameters.

* read set of filenames that are now sorted and generate macro variables ;
data work.use_date;
set work.use_date end=EOF ;
CALL SYMPUT('file' || trim(left(_n_)), trim(file_name) ) ;
CALL SYMPUT('crdate' || trim(left(_n_)) , PUT(file_last_written_date,date7.)) ;
if EOF then CALL SYMPUT('filecount', _n_) ;
run;

* this will give you macros file1, file2, etc. ;
%put file count = &filecount ;
%put file1 = &file1, crdate1 = &crdate1 ;

** NEW: Driver macro that will loop through list of files ;
%macro LoopFiles ;
%if &filecount > 0 %then %do ;
    %do i = 1 %to &filecount ;
       %check_new_date(&&file&i, &&crdate&i); *notice recursive macro syntax - resolve &i then resolve &file1, etc..;
    %end ;
%end ;
%mend LoopFiles ;

Your macro:

%macro check_new_date(thisfile, thiscrdate);

%if "&thiscrdate"d = %sysfunc(today()) %then %do;

data _null_;

abort return;

run;

%else %do;

data check1;

.....

%mend ;

** call driver macro ;

%LoopFiles;

Notice that I have used double-quotes around the date macro variable, and convert it from a text string to a SAS date with the "d' syntax.

I hope that you will consider trying my driver macro approach, with the parsing of the piped filelist outside of (and above) your macro.

Re: using proc contents variable

jimksas — Thu, 24 Apr 2014 18:11:22 GMT

Thanks a lot cwilson - it worked great...i just used proc sql to get "crdate" as macro variable after your first data step and proc print step. And then my macros....

Thanks all...

Re: using proc contents variable

jimksas — Thu, 24 Apr 2014 22:37:28 GMT

hi cwilson - however, i am still not sure how you explain below steps in your above code...

* this will give you macros file1, file2, etc. ;

%put file count = &filecount ;

%put file1 = &file1, crdate1 = &crdate1 ;

Re: using proc contents variable

cwilson — Thu, 24 Apr 2014 22:48:24 GMT

After you produce the sas dataset, work.use_date, that contains one row for each file in your file list, the next data step uses CALL SYMPUT to create macro variables. It uses the _n_ variable as a suffix on the macro variable name. I am creating a macro variable for the name of the file, and another macro variable for the create date. Finally, at the end of the file (flagged by end=EOF), I output a macro variable with the total number of files, which I then use to loop through the list.

So if you have 5 files, you will have the following set of macro variables:

&file1 &crdate1

&file2 &crdate2, etc.

&file5 &crdate5

&filecount (which = 5)

The %PUT statement is just to show the values for a few variables as a QC that it's working as desired.

Re: using proc contents variable

jimksas — Thu, 24 Apr 2014 23:45:45 GMT

okay i understand that part but is there any other way to loop every .mdb files (lets's say we have 5 files to check date and then condition) with crdate...

------------------------------------------------------------------------------------

*Your macro;

%macro check_new_date(thisfile, thiscrdate);

%if "&thiscrdate"d = %sysfunc(today()) %then %do;

.....

-----------------------------------------------------------------------------------------------------

*if i alter your above piece of code like this; /*this is from log*/

-----------------------------------------------------------------------------------------------------

%macro LoopFiles_and_trans (thisfile, thiscrdate);

%if &filecount > 0 %then %do ;

%do i = 1 %to &filecount ;

%check_new_date(&&file&i, &&crdate&i); *notice recursive macro syntax - resolve &i then resolve &file1, etc..;

180

Warning: Apparent invocation of macro CHECK_NEW_DATE not resolved...

%end ;

%if "&thiscrdate"d = %sysfunc(today()) %then

%do;

---------------------------------------------------------------------------------------------------------------------------------------------

/*i am getting error*/

ERROR: 180-322: statement is not valid or it is used out of proper order...

- Thanks!