Hi all, I want to import over 10 Excel files into SAS, and they have the same file name other than their serial number, like data1, data2, data3, ... data15. How can I use macro to import them to SAS? %macro imp_data(); %do i=1%to 15; proc import out=data&i datafile = "C:\data\data&i.xls"; run; %end; %mend; %imp_data(); run; Thanks a lot. ... View more

In the following file, I need to identify any "Paid" that happens after the first "Shipped", in terms of both order and time.  For example, for OrderID#1, the third record counts as it occurs after at least one shipment takes place. OrderID#2 does not have any record that counts because "Paid" cannot happen on the same day as "Shipped". For OrderID#4, its third record counts as "Paid" is supposed to occur after at least one "Shipped" and it needs be on the days after "Shipped". The last column "Completed" is my target. Any suggestions? OrderID Shipped Paid date Completed 1 0 1 1/1/2014 0 1 1 0 1/2/2014 0 1 1 1 1/5/2014 1 2 1 1 1/2/2014 0 2 1 0 1/4/2014 0 3 1 0 1/5/2014 0 3 1 1 1/6/2014 1 3 1 0 1/8/2014 0 4 1 0 2/2/2014 0 4 1 1 2/2/2014 0 4 0 1 2/5/2014 1 4 1 0 2/6/2014 0 Thanks a bunch! Lizi ... View more

Hi all, I have to admit my do-loop skill is too weak. I need to sort out the first and last months when shipping was made for each year within a year. As shown below, the columns of startmon and endmon are my objective variables I want. OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year startmon endmon 1 4 1 1 1 1 0 0 0 0 0 0 0 0 2012 1 4 2 11 0 1 1 1 1 1 1 1 1 1 1 1 2012 2 12 3 12 1 1 1 1 1 1 1 1 1 1 1 1 2012 1 12 4 1 1 0 0 0 0 0 0 0 0 0 0 0 2012 1 1 5 6 0 0 0 1 1 1 1 1 1 0 0 0 2012 4 9 Also, for the orders across years as below, I need to abstract a separate file with startmon, endmon, startyr and endyr. Original data: OrderID mons mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12 year 1 12 12 1 1 1 1 1 1 1 1 1 1 1 2011 3 3 0 0 0 0 0 0 0 0 0 1 1 1 2011 4 11 0 1 1 1 1 1 1 1 1 1 1 1 2011 1 4 1 1 1 1 0 0 0 0 0 0 0 0 2012 2 11 0 1 1 1 1 1 1 1 1 1 1 1 2012 3 12 1 1 1 1 1 1 1 1 1 1 1 1 2012 4 1 1 0 0 0 0 0 0 0 0 0 0 0 2012 5 6 0 0 0 1 1 1 1 1 1 0 0 0 2012 1 12 1 1 1 1 1 1 1 1 1 1 1 1 2013 2 10 1 1 1 1 1 1 1 1 1 1 0 0 2013 3 2 1 1 0 0 0 0 0 0 0 0 0 0 2013 5 12 1 1 1 1 1 1 1 1 1 1 1 1 2013 Objective data: OrderID startmon endmon staryr endyr 1 1 4 2011 2012 2 2 10 2012 2013 3 10 2 2011 2013 4 2 1 2011 2012 5 4 12 2012 2013 Your ideas are greatly appreciated! Lizi ... View more

I would like to count numbers of distinct cases that meet different criteria in my large dataset. The example data looks like below: obsid var 1 a 1 b 1 b 2 b 2 b 2 c 2 c 3 a 3 a 4 c I need to see how many distinct obsids that contain "a", "b" or "c" in var. I could do for different criteria one after another like this: proc sql; select count(distinct obsid) as N_a from project.dswi_nodebride where var="a"; run; proc sql; select count(distinct obsid) as N_b from project.dswi_nodebride where var="b"; run; proc sql; select count(distinct obsid) as N_c from project.dswi_nodebride where var="c"; run; I have many criteria, so is there a simpler way to do for all different but similar criteria? Thanks a lot! ... View more