creating macro variable for multiple value

[Tom · Accepted solution]

One question maybe answered. Are you getting the multiple dates from reading the content of the files rather than by taking the modified date of the files from the output of the DIR command?

I am still not sure how you are distinguishing the files.  You seem to want to find the files that start with K_FIRST_FILE or K_SECOND_FILE etc.

Are those really the names? Or if not the real names is the list of names constant or are are you looking for a solution where the list of filenames (or are they just filename prefixes) are passed into the system?  Is the list of names always 4 names long or do you want it flexible to handle different numbers of expected files?

Again to me the solution is to generate a data set with one row per file*date combination and use that to drive the report as in my previous post.

But if you want to try to stick to macro variables then you can use PROC SQL to create a single macro variable with the full list of dates (as long as it is not so long as to make the macro variable longer than the limit.).  Basically in your program you would replace this code :

proc sort data=d; by descending upload_date; run;

data d; set d(obs=1); call symput("upload_4",put(upload_date, date9.));call symput("m_name",m_name);run;

with a single PROC SQL step.

proc sql noprint ;

%let upload_4=;

select distinct upload_date format=date9.

  into :upload_4 separated by ', '

  from d

  order by date descending

;

quit;

[Tom]

Is your problem really that you do not know how to read the contents of variable number of files from a directory?

Why not just read the files in and skip the directory contents?

data dates ;

  length fname filename $200;

  infile "&dir1\*.txt" dsd dlm='|' filename=fname eov=eov ;

  input @;

  filename=fname;

  if _n_=1 or eov then input ;

  input date date9. ;

  format date date9.;

  eov=0;

run;

proc sort data=dates nodupkey ;

  by filename descending date ;

run;

data _null_;

set dates ;

by filename ;

if first.filename then put filename= ;

put date=;

run;

filename=c:\downloads\folder1\k_first_file.txt

date=05OCT2014

date=04OCT2014

date=03OCT2014

filename=c:\downloads\folder1\k_second_file.txt

date=05OCT2014

date=04OCT2014

date=03OCT2014

[woo]

Thank you Tom.

Here is my problem in simple way ...

/*lets assume that i am receiving "|" separated "class" text file (sashelp.class) on daily bases from you and i don't know how many distinct age will be there on different day (assume we will have different distinct age) and i want to show third party that class file uploaded today where we have n number of distinct age and those age are: 11 12 13 or whatever*/

/*lets say i am going to set code to run automatically on daily bases at x time*/

/*so first i am going read file in*/

data class;

set sashelp.class;

run;

/*then i am going pull distinct age from class table*/

proc sql;

create table dist_age as select distinct Age from class;

run;

/*so for today's case, dist_age table looks like this*/

Age

11

12

13

14

15

16

/*now i want to send email to team saying that*/

Hi Team, we receive class file today which has 6 distinct age which are: 11,12,13, 14, 15, 16.

/*but tomorrow message would be different*/

Hi Team, we receive class file today which has 2 distinct age which are: 21, 22.

/*so basically i am looking for one macro variable which i can write in message like*/

filename mymail email;

subject="..............";

to=".....";

data _null_;

set datasetname;

file mymail;

put "Hi Team, we receive class file today which has &n distinct age which are: &xyz_macrovariable";

/*put Hi Team, we receive class file today which has 6 distinct age which are: 11,12,13, 14, 15, 16*/

run;

Thank you so much for staying with me Tom...!!!

[Tom]

IF you want to create A macro variable with multiple values then the code I posted using PROC SQL with the INTO and SEPARATED BY clauses is the easiest way.  You can use the automatic macro variable SQLOBS to find the N.

IF you want to produce a REPORT (or an EMAIL message) with multiple values then the code I posted using a DATASET is the easiest way since you will not need to use macro variables at all.

[woo]

got it. thanks a lot...Tom...lot to learn from your code...glad we have u on community...