I have a script that 95% of the time works perfectly but every now and then there is an error that causes the entire program to stop.    My code runs 42 regressions and the results from these regressions are placed in 42 individual tables and then those 42 tables are appended to one another to make a master table. Some times there is no data for 1 or more of the 42 regressions so no table is produced. If one or more of the 42 tables does not exist, the program hits an error when the 42 tables are appended together.   data work.appended; set work.monday_em_t work.monday_da_t work.monday_ef_t work.monday_pr_t work.monday_lf_t work.monday_ov_t work.tuesday_em_t work.tuesday_da_t work.tuesday_ef_t work.tuesday_pr_t work.tuesday_lf_t work.tuesday_ov_t work.wednesday_em_t work.wednesday_da_t work.wednesday_ef_t work.wednesday_pr_t work.wednesday_lf_t work.wednesday_ov_t work.thursday_em_t work.thursday_da_t work.thursday_ef_t work.thursday_pr_t work.thursday_lf_t work.thursday_ov_t work.friday_em_t work.friday_da_t work.friday_ef_t work.friday_pr_t work.friday_lf_t work.friday_ov_t work.saturday_em_t work.saturday_da_t work.saturday_ef_t work.saturday_pr_t work.saturday_lf_t work.saturday_ov_t work.sunday_em_t work.sunday_da_t work.sunday_ef_t work.sunday_pr_t work.sunday_lf_t work.sunday_ov_t; run;   For instance, if there is no "work.saturday_em_t" table, the code above will create an error and not produce an appended file.    I would like for the code to just ignore if a table is missing and append all tables that do exist without stopping the program.    Is this possible?      ... View more

I'm working with a stored procedure that pulls data via an ODBC connection  using proc sql. In particular, there are 3 columns that hold values for which I would like to shape my query in either the proq sql step or a secondary data step. The columns are basically hierachical, Company, then Product, then Campaign. Sometimes there Company, Product, and Campaign values are all the same while other companys might have X amount of products and Y amount of campaigns.    I'd like to be able to pass values to the macro via "%let" statements, but sometimes it is unnecessary and/or redundant to give values for Product and Campaign. Other times I might want to specify one product and many times I will want to specify 1 or more campaigns.    The goal is to automate the query such that we can add in varying numbers of macro parameters such that if no value for a particular column is passed the query simply does not use it as a condition.    For example:   %let Company  = ABCD; %let Product = ; %let Campaign = ;   proc sql; connect to odbc (dsn='DW' uid='**********' pwd='*************'); create table work.table as select *  from connection to odbc (execute GETDATA "&Company","&Product","&Campaign") ; disconnect from odbc; quit;     In the case above, I would simply want the query to return me all data associated with Company ABCD. Since no Product of Campaign was assigned (or it's blank/missing), I would not want the query to subset the data based on those columns.    Additionally:   %let Company  = EFGH; %let Product = ZYX; %let Campaign = DEF FED;   proc sql; connect to odbc (dsn='DW' uid='**********' pwd='*************'); create table work.table as select *  from connection to odbc (execute GETDATA "&Company","&Product","&Campaign") ; disconnect from odbc; quit;     In the case above, I would simply want the query to return me all data associated with the EFGH Company for their product ZYX and the campaigns for that product of DEF and FED.   It seems like it might be a better idea to execute this in a data step after the full data set has been brought in. Does anyone have an suggestions on dealing with optional macro parameters?    Thanks in advance.    Michael ... View more

Hello,   I am working with a time series data set and am hoping someone can point me in the right direction for specifying a model considering my data.    My dependent variable is the number of web visits that hit a website on a daily interval for about 1.5 years ~500 observations.    My independent variables are the value of television dollars spent on about 250 different television stations. The television stations range from small reach stations to very large reach stations. Since I cannot place 250 independent variables into a regression anaysis with 500 observations I have chosen to group (cluster) stations together that are closely related in terms of reach or average price per television station. I have grouped my station variables into 7 different groups.   I am initially trying to  model this with an ARIMAX with distributed lags to pick up any drag behavior. However, what I am finding is that I am getting negative coeeficient for some of the group variables which theoritically does not make any sense. What I believe is happening is that the the regression is picking up the shift from an efficient TV group to a less efficient TV group; thus, it is showing me the net effect of dollars being shifted. What I am finding is that my "group" variables are additive functions of one another....as in   Cost_Group1 = Total Budget - Cost_Group2 Cost_Group3 Cost_Group4 Cost_Group5 Cost_Group6 Cost_Group7.   So if the true effect of 1 dollar (Group7)= 100 and the true effect of 1 dollar(Group1) = 10....then if there is a shift of dollars to Group1, the net effect is -90 and that's not actually true.   So it appears my independent variables are actually endogenous with one another....   any help?  ... View more

Hi All,    Not looking for a lesson in stats as I am perfectly capable of researching statistical methods and I do have a background in econometrics and stats. I would however like to ask if anyone can point me in a good direction or even just giev me the name of the class of models I am describing.    I have data sets that typically ranges from 1000-8000 observations. Typically there over 50-200 individuals (in my case they are marketing channels) and each has a multiple observations. Because the individual effects of each of these observations are typically grouped with the individual effects of multiple individuals, the individual effect of a station is not isolated. Instead, I buckets (if you will) of effects that I know came from Individual A, B ,G, E, and F.   For example, I have 8000 Unique effects (observations) caused by 100 Individuals. On average, 5 random individuals have an effect at one time...thus, I have  8000/5 groups = 1600 groups.    Group 1: Individual A, Individual B, Individual C, Individual D, Individual E Effect of Group 1: $5000   Group 2: Individual A, Individual C, Individual G, Individual H, Individual L Effect of Group 2: $4,000   Group 3: Ind. Z, E, Y, D, Q Effecot of Group 3: $8,000   So above I have 3 groups of 5 individuals whose aggregated effects are observed. My goal is to use the entire dataset to determine how much of the effect of each group was due to the individuals of the group.    For Group 1, the end goal would be something like:  Ind. A = 40% = $2000 Ind. B = 10% = $500 Ind. C = 15% = $750 Ind. D = 20% = $1000 Ind. E = 15% = $750   I am assuming this is going to be a probablistic model and possible a panel data model.   Any suggestions?      ... View more