topic Re: Macro help in SAS Enterprise Guide

Macro help

woo — Tue, 14 Jul 2015 23:42:30 GMT

please help guys,

i have below macros resolves to 3 different files with .txt extension like,

macro file1 resolves to: text_file1.txt

macro file2 resolves to: text_file2.txt

macro file3 resolves to: text_file3.txt

each text file has variable called "b_date" (date format looks like this: 20150714)

text_file1.txt has 2 distinct date (20150714, 20150713) data

text_file2.txt has 1 distinct date (20150712) data

text_file3.txt has 3 distinct date (20150711, 20150710, 20150709) data

I want to send email to team mentioning which file has what distinct date like this,

filename mymail email;

subject="my_subject"

to="my_company@mail.com";

data _null_;

file mymauk;

put "";

put text_file1.txt has 20150714, 20150713 date data";

put text_file2.txt has 20150712 date data";

put text_file3.txt has 20150711, 20150710, 20150709 date data";

run;

how can i link or create message like in red (using macros),

Thank you..........

Re: Macro help

ballardw — Wed, 15 Jul 2015 16:15:30 GMT

First step is likely to be to import/read the text files into SAS.

By distinct do you mean that date only occurs once within the file? Or are you specifying one or more dates to search for?

Is a solution that does not involve macros acceptable?

Re: Macro help

woo — Wed, 15 Jul 2015 16:35:34 GMT

i am importing text files and finding distinct date from file, some file may have 2 distinct dates data and some file may have 5 distinct date data.

so here i have file name macro and related distinct date macro ready but don't know the final step to include in message, so basically i will need to link file name macro and related distinct date macro,

put text_file1.txt has 20150714, 20150713 date data";

put text_file2.txt has 20150712 date data";

put text_file3.txt has 20150711, 20150710, 20150709 date data";

Re: Macro help

Astounding — Wed, 15 Jul 2015 22:45:33 GMT

Are you talking about this:

put "text_file1.txt has &file1 date data";

Re: Macro help

Tom — Thu, 16 Jul 2015 00:17:25 GMT

Are the TEXT files consistently formatted? How are you to read them? Let's assume that they are CSV files with consist columns names and order of columns.

Then you can read them into a single SAS dataset. For simplicity let's just assume that they only have the one column which as the DATE and there is no column header in the text file. (It is simple to adjust the program to deal with both of these issues.

Do you care if the DATE values are valid dates or not? Can we just treat them as text strings? For now we will do that, but it is not hard to treat them as dates.

So first let's generate some dummy data and create the macro variables FILE1 , FILE2 and FILE3 .

let work = %sysfunc(pathname(work));

data _null_;

do i=1 to 3 ;

fname = "&work/" || cats('text_file',i,'.txt') ;

call symputx(cats('file',i),fname);

file csv filevar=fname dsd ;

if i=1 then do date='20150714', '20150713';

put date;

end;

if i=2 then do date='20150712','20150712';

put date;

end;

if i=3 then do date='20150711','20150710','20150710','20150709';

put date;

end;

run;

Now let's read them into a data set and sort by FILENAME and DATE .

data all ;

infile cards truncover;

input fname $200. ;

fname=resolve(fname);

filename=fname ;

do while (not eof);

infile dummy filevar=fname dsd truncover end=eof ;

length date $8 ;

input date;

output;

end;

eof=0;

cards;

&file1

&file2

&file3

;;;;

proc sort;

by filename date ;

run;

Now replace your DATA _NULL_ with this step to generate the report from the data.

data _null_;

file mymail ;

set all ;

by filename date ;

if first.filename then put filename :$quote. 'has dates ' @ ;

if first.date then put date @ ;

if last.filename then put ;

run;

Re: Macro help

MadhuKorni — Thu, 16 Jul 2015 07:42:56 GMT

Lets import the three files( Filenames : File1,File2,File3) into three datasets(Datasetnames : File1,File2,File3).

As mentioned b_date is a date variable in all the three files.

proc sql;

select distinct b_date into : file1Dates separated by ',' from File1;

select distinct b_date into : file2Dates separated by ',' from File2;

select distinct b_date into : file3Dates separated by ',' from File3;

quit;

filename mymail email

subject="my_subject"

from = "xyz@gmail.com"

to="my_company@mail.com"

type = text/html;

Make sure that ; should be used as termination to the filename statement. You used it twice in the statement.

data _null_;

file mymail;

put "";

put "text_file1.txt has &file1Dates date(s) data";

put "text_file2.txt has &file2Dates date(s) data";

put "text_file3.txt has &file3Dates date(s) data";

run;

Re: Macro help

woo — Mon, 20 Jul 2015 21:59:14 GMT

Thank you Madhu but here for me two things are uncertain day to day, number of datasets (for files) are not fixed and number of distinct date in each file is not fixed,

so in this case, i am not sure how we can writ below statement and loop everything,

proc sql;

select distinct b_date into : file1Dates separated by ',' from File1;

select distinct b_date into : file2Dates separated by ',' from File2;

select distinct b_date into : file3Dates separated by ',' from File3;

quit;

Re: Macro help

Patrick — Tue, 21 Jul 2015 01:43:15 GMT

You have some code which extracts data from a varying number of external files into SAS data sets on a daily basis.

1. Do these daily SAS datasets have some naming convention so that it would be easy to determine which ones have been created "today"?

2. Are the dates you're interested in stored in a variable with the same name in all of these daily SAS data sets?

If 1) and 2) are met then it wouldn't be too hard to pull out all distinct dates and generate an email. Ideally: You also store the name of the source external files in the SAS data sets.

Re: Macro help

RW9 — Tue, 21 Jul 2015 08:54:02 GMT

This then is your first problem:

"for me two things are uncertain day to day, number of datasets (for files) are not fixed and number of distinct date in each file is not fixed,"

Where is your data transfer agreement, a document which details what data will be supplied? If you don't know your own data, you can't write a useable generalised macro.

Secondly, if the list of dates is unlimited, then you would be better served by using a complete dataset with the information and then generating the code, for example, after importing the text files (see above point), I end up with a dataset LOOP:

FILE DATE

text1 20150604

text1 20140304

text2 20140506

...

I can then use this to generate the necessary email code:

filename myemail email subject="my_subject" to="my_company@mail.com";

data _null_;

set loop end=last;

by file;

if _n_=1 then call execute('data _null_; file myemail; put "";');

if first.file then call execute(' put '||strip(file)||' has '||strip(date));

else call execute(', '||strip(date));

if last.file then call execute(';');

if last then call execute(';run;');

run;

Re: Macro help

woo — Tue, 21 Jul 2015 16:29:46 GMT

this is the scenario;

-> i receive text files, may be one or more with same variable and format, i will need one variable/field for my process which is "date" which come in format like 20150721. different file has different date data as i mentioned above

text_file1.txt has 2 distinct date (20150714, 20150713) data

text_file2.txt has 4 distinct date (20150712, 20150711, 20150710, 20150709) data

-> i read them in and create SAS datasets like text_file1/textfile_2/.........text_file_5

-> i create datasets like data1/data2/....data5 with distinct date date (so if 5 text files, 5 sas datasets (read in), 5 datasets (with distinct date)

now lets say i receive 5 text files;

-> i read them in and have 5 datasets

text_file1

text_file2

text_file3

text_file4

text_file5

-> i created another 5 datasets (from text_file&i.) (with one variable "b_date" with distinct date)

data1 ("b_date" variable has 2 distinct date 20150714, 20150713)

data2 ("b_date" variable has 4 distinct date 20150712, 20150711, 20150710, 20150709)

data3 ("b_date" variable has 1 distinct date 20150708)

data4 ("b_date" variable has 1 distinct date 20150707)

data5 ("b_date" variable has 3 distinct date 20150706, 20150705, 20150704)

NOW, TOMORROW I MIGHT RECEIVE ONLY 2 TEXT FILES AND NOT 5 AND EVEN MAY BE WITH SAME DATE DATA

So this could be the message for team,

Hi Team,

text_file1 has 2 distinct date 20150714, 20150713

text_file2 has 4 distinct date 20150712, 20150711, 20150710, 20150709

text_file3 has 1 distinct date 20150708

text_file4 has 1 distinct date 20150707

text_file5 has 3 distinct date 20150706, 20150705, 20150704

Re: Macro help

Tom — Tue, 21 Jul 2015 17:01:20 GMT

A good general method is use some way to get the list of files into a DATASET. Then you can use the dataset to drive the other steps. You do NOT need to use macros or macro variables to do this as you can use CALL EXECUTE() to generate the code. Or use PUT statements to write code to a text file that is then %INCLUDED.

Since your files seems to all have the same data it would easiest if in the processing your generated a single DATASET that had the filenames and dates. Then your step 3 to write the email is has been posted above many times and does NOT depend on how many files were read that day.

So break it into pieces.

1) How can you get the list of files to process. If you can run operating systems command then this usually the easiest way. Use ls or find on Unix or using DIR on Windows.

2) How can you process each file. That depends on the content of the files. If they are just text files then read them with a data step. If they are EXCEL files or other structured files then you need do something more complex.

3) Combine them and find unique dates. You can do it in either order. If the files are large and have many duplicate dates then find the unique dates before combining them. But your files would need have thousands of duplicate dates to make it worth the effort. The advantage of read text files is that the process reading and combining could be combined into a single data step.

4) Produce the output email using a data step with BY variable processing to write the report.

Re: Macro help

woo — Tue, 21 Jul 2015 18:50:05 GMT

Thank you so much Tom and other for your inputs,

For now i what i did is after creating datasets with distinct date, i created one single dataset using "data" and "set" statement which has all distinct dates from all different datasets and then used proc SQL to create macro variable which link to distinct date, separated by ', it worked fine,

But i wanted to send email to team mentioning which particular text file has what distinct date data, but that's fine, looks little difficult for me , will see later if i can do it,

Thank you all again

Re: Macro help

Tom — Tue, 21 Jul 2015 19:49:15 GMT

If you have a single dataset with all filenames and dates then there is no need for generating macro variables.

data _null_;

file mymail ;

set all ;

by filename date ;

if first.filename then put filename :$quote. 'has dates ' @ ;

if first.date then put date @ ;

if last.filename then put ;

run;

Re: Macro help

woo — Tue, 21 Jul 2015 19:58:38 GMT

thank you Tom...