/*This data step has worked for me  - i think we don't need two separate data step for it*/ options validvarname = Any; data test;   attrib 'Name with blank'n length = $10;     rename 'Name with blank'n = Name_with_blank;     set test; run; @ Reeza - your code ran fine too... Thanks a lot all of you ... View more

Encoding = wlatin1 Western (Windows) Engine = V9 Indexes= 0 OBS length=432 ... View more

proc contents data=have varnum ; run; #     variable                                    type   len   format   informat   label 1     finance field                             char   16                                       finance field  2     marketing strategy                 char    25                                      marketing strategy  3     goal to realign                         char    20                                      goal to realign                                   4     process in place                     char    25                                      process in place Dataset is already existed and i don't know from which source (row file) or i would reread with my own variable say using "_" in b/w ... View more

i did try that way too but getting both variable into the final dataset; finance field  | finance_field where "finance field" has value and "finance_field" doesn't have anything.... Also validvarname=any giving that WARNING MESSAGE WARNING: Only Base procedures and SAS/STAT procedures have been tested for use with VALIDVARNAME=ANY. Other use of this option is considered experimental and may cause undetected errors. ... View more

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

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...!!! ... View more

K Tom my be i didn't represent very well... forget about that - forget about control table and all Here is the real coding options mprint mlogic symbolgen; %let ext=txt; %let dir1=w:\data\in\; %let dir2=w:\shares\woo\; data f_list1; length fname1 $200; infile "dir &dir1.*.&ext /tw " pipe truncover lrecl=3000 ; input fname1 $200.; run; data f_list2; length fname2 $200; infile "dir &dir2.*.&ext /tw " pipe truncover lrecl=3000 ; input fname2 $200.; run; data a; format upload_date date.; set f_list1; if find(upcase(fname1),upcase("k_first_file"))>0; r_name=substr(upcase(fname1),find(upcase(fname1),upcase("k_first_file"))); upload_date=input(substr(fname1,1,10),mmddyy10.); run; proc sort data=a; by descending upload_date; run; data a ; set a(obs=1); call symput("upload_1",put(upload_date, date9.)); run; data b; format upload_date date.; set f_list2; if find(upcase(fname2),upcase("k_second_file"))>0; r_name=substr(upcase(fname2),find(upcase(fname2),upcase("k_second_file"))); upload_date=input(substr(fname2,1,10),mmddyy10.); run; proc sort data=b; by descending upload_date; run; data b; set b(obs=1); call symput("upload_2",put(upload_date, date9.)); run; data c; format upload_date date.; set f_list1; if find(upcase(fname1),upcase("K_third_file"))>0; f_name=substr(upcase(fname1),find(upcase(fname1),upcase("K_third_file"))); r_name=(substr(fname1,40,150)); /*pulling full name for file "K_third_file" to use it later in filename statement*/ r_name=trim(left(r_name)); upload_date=input(substr(fname1,1,10),mmddyy10.); run; proc sort data=c; by descending upload_date; run; data c; set c (obs=1); call symput("upload_3",put(upload_date, date9.)); call symput("r_name",r_name); run; /*Read "K_third_file" file in and figure how many distinct date there in file*/ filename xyzin "&dir1.&r_name."; data file_in_1; infile xyzin dsd delimiter='|' firstobs=2; length date $ 20        x $ 10        y $ 10     z $ 10 input  date $        x $        y $     z$     ; run; proc sql; create table dist_date_c as select distinct date from file_in_1; quit; /*i stuck from here*/ /*here lets say table "c_uni_date" has 3 distinct date which is not a case all time - could be 1 distinct date or 2 or 3*/ /*so need to create macro variable for all 3 dates (if 3 distinct date) and need to show in email...*/ /*not sure but we can do this - we can create one new macro variable X here which include n number of date macro variable (3 in this case) so that we can mentioned &x in message to show 3 different macro variable which would resolve in 3 dif. date...like "&&x&daten"=date1 date2 date3*/ data d; format upload_date date.; set f_list1; if find(upcase(fname1),upcase("k_forth_file"))>0; f_name=substr(upcase(fname1),find(upcase(fname1),upcase("k_forth_file"))); m_name=(substr(fname1,40,150)); /*pulling full name for file "K_forth_file" to use it later in filename statement*/ m_name=trim(left(m_name)); upload_date=input(substr(fname1,1,10),mmddyy10.); run; 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; filename abcin "&dir2.&m_name."; data file_in_2; infile abcin dsd delimiter='|' firstobs=2; length date $ 20        a $ 10        b $ 10     c $ 10 input  date $        a $        b $     c $     ; run; proc sql; create table uni_date_d as select distinct date from file_in_2; quit; /*here lets say table "c_uni_date" has 3 distinct date which is not a case all time - could be 1 distinct date or 2 or 3*/ /*same thing here - we can create one new macro variable X here which include n number of date macro variable (3 in this case) so that we can mentioned &x in message to show 3 different macro variable which would resolve in 3 dif. date...like "&&x&daten"=date1 date2 date3*/ %let file1=k_first_file; %let file2=k_second_file; %let file3=k_third_file; %let file4=k_forth_file; filename mymail email       subject="All file detail"       to= ("xyz@yahoo.com");          data _null_;   set a;   set b;   set c;   set d;         file mymail;         put "&file1 uploaded on &upload_1";   put " ";      put "&file2 uploaded on &upload_2";      put " ";      put "&file3 uploaded on &upload_3 with distinct date: /*numbers of distinct date goes here - may be 1 or 2 or 3*/";      put " ";   put "&file4 uploaded on &upload_4 with distinct date: /*numbers of distinct date goes here - may be 1 or 2 or 3*/";   run; ... View more

Hello Tom, thanks for your detail code but i am still struggling to get this done. So i have done following things. I just need help in using my macro value to send out email to team. /*Create "control tbl" for message */ Dataset name "Control_tbl" Alert    Message 1          Hello team, today we received files as below 2          file_1 arrived today with 3          file_1 has not arrived today 4          file_2 arrived today with 5          file_2 has not arrived today 6          file_3 arrived today with 7          file_3 has not arrived today 8          file_4 arrived today with 9          file_4 has not arrived today /*Read all txt files for DIR1*/ Dataset name "A" /*Read all txt files for DIR2*/ Dataset name "B" /*step1*/ data c; set a; if find specific file; upload_date=.......; run; proc sort data=c; by descending upload_date; run; data c; set c (obs=1); call sumput (upload_date1, put(upload_date, date9.)); runl /*established macro variable &upload_date_1 which has value for lets say today's date (alert=2 message goes out from control table) OR if file not present then this macro will not resolve so alert=3 message goes out from control table*/ /*step2*/ dataset name d same as step 1 for different specific file and created &upload_date_2 which has value for lets say today's date (alert=4 message goes out from control table) OR if file not present then this macro will not resolve so alert=5 message goes out from control table /*************** i completed work until here*********since know there will be only one date for file **************Need help from here please*******************/ /*step3*/ - read specific one particular file in from dataset A which has lot dif. text file (key var=date) - pull out distinct date using proc sql - now we don't know how many distinct date here will be - may be 1 or 2 or 3 so need to create macro variable for all available distinct "date" Alert=6 message goes out from control table and if file not there on directory then alert=7  message goes out /*step4*/ SAME CASE AS STEP3 for different specific file Alert=8 message goes out from control table and if file not there on directory then alert=9 message goes out END /*This is the sample message*/ Hello team, today we received files as below file_1 arrived today with 06Oct14 file_2 has not arrived today file_3 arrived today with 04Oct14, 05Oct14, 06Oct14 file_4 arrived today with 05Oct14, 06Oct14 /*does this idea looks feasible to you - please let me know when you get chance.*/ Thanks! ... View more

Tom i have following code; %let ext=txt; %let dir1=w:\woo\testdir1\; %let dir2=w:\woo\testdir2\; data test1; length fname1 $200; infile "dir &dir1.*.&ext /tw" pipe truncover lrecl=3000; input fname $200.; run; data test2; length fname2 $200; infile "dir &dir2.*.&ext /tw" pipe truncover lrecl=3000; input fname $200.; run; THEN SOME DATA STEPS TO GET MODIFIED DATE FOR FILE1 AND FILE2 &file1 with &x_date; /*file1 with 29sep14*/ &file2 with &y_date; /*file2 with 28sep14*/ /*your logic*/ /*i am trying to include my PUT statement along with your logic*/ proc sort data=HAVE (keep=date) nodupkey out=udates;   by date ; run; filename mymail email subject="no. of dif date in file" to=("email1" "email2" "emailn... "); data _null_;   set have ;   file mymail;   if _n_=1 then put "hello team, we have data for the following dates:";   put date ;    put &file1 with &x_date;    put &file2 with &y_date; run; ... View more

Thanks a lot Tom - i got that... sorry to drag you into this issue but have issue in how email shows message: i am also including other macro variable value into message (&x_date and &y_date) /*&x_date=29sep14 and &y_date=28sep14*/ /*you suggested resolves to*/ 20140930 20141001 /*so final filename statement looks like this*/ filename mymail email       subject="xxxxxxxxxxxxxxxxxxxxxxxxxxxx"       to= ("xxxxxxxxxxxxxxxxx");         data _null_;         set have;               file mymail;               if _n_=1 then put "hello team, we have data for the following dates:";               put date;               put "&file1 with &x_date"; /*my additional  macro variable*/               put "&file2 with &y_date"; /*my additional  macro variable*/   run; email shows like this: ____________________________________________________ "hello team, we have data for the following dates:"; 20140930 file1 with 29sep14 file2 with 28sep14 20141001 file1 with 29sep14 file2 with 28sep14 ___________________________________________________ ----------------------------------------- it should show dates only once, like this, right?: "hello team, we have data for the following dates:"; 20140930 20141001 file1 with 29sep14 file2 with 28sep14 ---------------------------------------- ... View more