I need  some help in reading the complex Excel file and  then once I have read it, I want to transpose the data. Note:   There are 2 blank lines before the data starts. Any help will be highly appreciated. Below is the sample Input: M_Type M_TYPE_DESC P_H_Desc MARKET_Type_Ind MAT ~ 09/2010 MAT ~ 09/2011 YTD ~ 09/2010 YTD ~ 09/2011 MAT ~ 09/2009 MAT ~ 09/2009 MAT ~ 09/2010 MAT ~ 09/2010 MAT   ~09/2011 MAT   ~09/2012 US US US US ABC.GROWTH XYZ.PRODUC ABC.GROWTH XYZ.PRODUC US US US US US US Growth~09/2010 Growth~09/2011 L Local Pharmacy E 350702456608.00 338338364318.00 260434737707.00 249355601999.00 81913057250.00 12863795856.00 18357058601.00 936889621.00 -3.53 -16.55 Output is as follows: M_Type M_TYPE_DESC P_H_Desc MARKET_Type_Ind Y_mnth MAT YTD ABC.GROWTH XYZ.PRODUC Growth~09/2010 Growth~09/2011 L Local Pharmacy E 201009 350702456608.00 260434737707.00 81913057250.00 12863795856.00 L Local Pharmacy E 201109 338338364318.00 249355601999.00 18357058601.00 936889621.00 -3.53 L Local Pharmacy E 201209 -16.55 ... View more

I have datetime variable.  For example:  start_date = '07JUN2014:00:54:00"   I want to subtract 3 hours from the above date in  PROC SQL. So output will be 06JUN2014:09:54:00 Can anybody help me in this? ... View more

I have several files that gets added to the folder daily. For example:  test140429.xlsx, test140430.xlsx, test00502.xlsx,test00503.xlsx, test00504.xlsx and so on.. In the above example test140429.xlsx (14 stands for year 2014, 04 -- April and  29 - day).   But May file I do not have year front of them for example test00502.xlsx, test00503.xlsx and so on.  I want to rename the files as test140502.xlsx, test140503.xlsx and so on.  I would appreciate if anybody can help me in this. I am using following code to read the files in the directory. %let src=c:\temp; data srcdata ;    infile "dir /b ""&src\"" " pipe truncover;      input fname $256. ;   if index(fname,"test")  >  0; run; ... View more

I am using the following code to send email through SAS if any of the datasets(daily)  are missing after checking if it is a working day or not based on the working day dataset.  Everything works but  it sends email  also even if wday_cnt = 0 (means non-working day).  Can anybody let me know what I am doing wrong.. proc sql noprint;   /** check if it is a working day or not *************/   select count(new_date) into :wday_cnt     from  wday_dsn     where  date1 = today();   /***** Check if  dsn2 data  exists for today or not **************/   select count(distinct var1) into :dsn1_var_cnt     from  dsn1     where dsn1_date = today(); /***** Check if dsn2 data exists for today or not **************/   select count(distinct var1) into :dsn2_var_cnt     from dsn2     where dsn2_date = today(); quit; %put wday_cnt=&wday_cnt.; %put dsn1_var_cnt =&dsn1_var_cnt.; %put dsn2_var_cnt=&dsn2_var_cnt.;    filename mail email from="myname@email.com";  data _null_;    if &wday_cnt. = 1  then do;     if &dsn1_var_cnt. = 0  and &dsn2_var_cnt. > 0 then do;         file mail       to=("myname@email.com")       cc=("xyz@email.com" "abc@email.com")   subject="Report Alert";      put " DSN1 data missing"; end;    if &dsn1_var_cnt. > 0  and &dsn2_var_cnt. = 0 then do;        file mail     to=("myname@email.com")   cc=("xyz@email.com" "abc@email.com")   subject="Report Alert";      put " DSN2 data missing"; end;    if &dsn1_var_cnt. = 0  and &dsn2_var_cnt. = 0 then do;        file mail      to=("myname@email.com")    cc=("xyz@email.com" "abc@email.com")   subject="Report Alert";      put " DSN1 and DSN2 data missing"; end;   end;   /****** Working day loop end ***********/  run; ... View more