Here's an example:   %let n=1; %let count1 = subcount1; %let subcount1 = 100; data test; set mor_filelist_ma; n="&n."; *Resolves to 1 as expected; count1="&count1."; *resolves to subcount1 as expected; subcount1="&subcount1."; *resolves to 100 as expected; test1="&count&n."; *resolves to subcount1 as expected; test2="&&&count&n."; *resolves to subcount1 - expecting 100; run; How do I get test2 to resolve to 100 instead of subcount1? I'm thinking this is easy and I'm just missing something... ... View more

I am trying to export SAS tables to an existing Excel template. The template includes two sheets - "Summarize", and "Test". I want to export the SAS data tables to existing sheets, or named ranges in the template. Here's some sample code:   %let reporting_template_file = \\XXX...\Test Report.xlsx; data test; infile datalines delimiter=','; length item $31. price 8.; input item $ price 8.; datalines; Blueberries, 1 Strawberries, 2 Huckleberries, 3 Raspberries, 4 Blackberries, 5 Blueberries, 3 Strawberries, 1 Huckleberries, 4 Raspberries, 5 Blackberries, 2 ; libname xl "&reporting_template_file."; data xl.test; set test; run; libname xl clear;   However, this approach creates a new sheet called "Test1" instead of writing to the existing "Test" sheet. This means that the template will not work as intended, because the "Summarize" sheet pulls off of the data that is supposed to exist in the "Test" sheet.     I understand that there are other solutions to this problem that do not depend on Excel(i.e. proc report). For this task, I am not using proc report. Is there any way to output my SAS tables to an existing sheet in Excel?   I have also tried outputting to xl."test$"n; this should specify that I want to output to the sheet "test". However, this returns the following error:    ERROR: The MS Excel table test$ has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option.   Similarly, I tried outputting to a previously created named range, xl.test_named_range; this should specify that I want to output to the named range "test_named_range". However, this returns the same error:   ERROR: The MS Excel table test_named_range has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option.   I was originally trying to export these tables using proc export. This method gives me the data in the right place but was taking nearly 50 minutes. All of the tables are very, very small - this process takes 15 seconds when using the libname xlsx method, but the data is in the wrong spot. Any ideas are much appreciated!  ... View more

I frequently create program processes using a master file that allow the user to define inputs for several programs and then run those programs sequentially. For a simple example, let's say that I had two programs that I wanted to run. Program (1) creates the data, and Program (2) summarizes it. I would then create a master file, Program (0), that contains the inputs for both programs and allows the user to run both. Each program is in a separate program file. Here is what it looks like: *Master File; %include "\\XXX\SAS Question\Pre-Defined Macros.sas"; %let program_path = \\XXX\SAS Question; *Program 1 Inputs; %let run1 = Y; %let path1 = &program_path\(1) Create Data.sas; *Program 2 Inputs; %let run2 = Y; %let path2 = &program_path.\(2) Summarize Data.sas; %let crop_data_table = sum_table_veggies; %let sum_vars = num; %let by_vars = vegetable; %let output_table = vegetable_summary; %if &run1. = Y %then %do; %include "&path1."; run; %end; %if &run2. = Y %then %do; %include "&path2."; run; %end; ***END OF MASTER FILE***; ***START OF PROGRAM (1)***; data sum_table_veggies; infile datalines delimiter=','; input vegetable $ num 8.; datalines; Carrot, 9 Rutabega, 22 Parsley, 98 Carrot, 13 Rutabega, 5 Parsley, 56 ; data sum_table_fruits; infile datalines delimiter=','; length fruit $10.; input fruit $ num 8.; datalines; Cantaloupe, 9 Raspberry, 22 Peach, 98 Cantaloupe, 13 Raspberry, 5 Peach, 56 ; ***END OF PROGRAM (1)***; ***START OF PROGRAM (2)***; %macro summarize(in_table,sum_vars,class_vars,out_table); proc summary data=&in_table. nway; var &sum_vars.; class &class_vars.; output out=&out_table. (drop=_:) sum=; quit; %mend; %summarize(&crop_data_table., &sum_vars., &by_vars., &output_table.); ***END OF PROGRAM (2)***;   Now, I want to run this process several times, passing the inputs to SAS programmatically. The way that I tried to do this is by macro-izing the master file itself, passing the inputs in as a data table, and using call execute to run the macro for as many observations as are in the inputs data table, as follows:     *Master Loop; %include "XXX\SAS Question\Pre-Defined Macros.sas"; %let Path = your_path_here; *Create inputs table (normally I would just import this from Excel); %global crop_data_table sum_vars by_vars output_table; data inputs; infile datalines delimiter=','; length var1 $121 var2 $1 var3 $20 var4 $1 var5 $22 var6 $17 var7 $3 var8 $9 var9 $17; input var1 $ var2 $ var3 $ var4$ var5 $ var6 $ var7 $ var8 $ var9 $; datalines; &Path.\SAS Question, Y,(1) Create Data.sas,Y,(2) Summarize Data.sas,sum_table_veggies,num,vegetable,vegetable_summary &Path.\SAS Question, Y,(1) Create Data.sas,Y,(2) Summarize Data.sas,sum_table_fruits,num,fruit,fruit_summary ; %include "&Path.\SAS Question\Loop Macro.sas"; *Use call execute to loop through the macro; data _null_; length callstr $350.; set inputs (obs=max); callstr = cats('%loop(var1=',var1,',var2=',var2,',var3=',var3,',var4=',var4,',var5=',var5,' ,var6=',var6,',var7=',var7,',var8=',var8,',var9=',var9,')'); call execute(callstr); run; ***END OF MASTER LOOP***; ***START OF LOOP MACRO***; *Loop Macro; %macro loop(var1, var2, var3, var4, var5, var6, var7, var8, var9); %let program_path = &var1.; %let run1 = &var2.; %let path1 = &var1.\&var3.; %let run2 = &var4.; %let path2 = &var1.\&var5.; %let crop_data_table = &var6.; %let sum_vars = &var7.; %let by_vars = &var8.; %let output_table = &var9.; %if &var2. = Y %then %do; %include "&var1.\&var3."; run; %end; %if &var4. = Y %then %do; %include "&var1.\&var5."; run; %end; %put _all_; %mend; ***END OF LOOP MACRO***;   However, when I run this series of programs, I get the following output tables:   Work.Inputs Work.Sum_Table_Veggies Work.Sum_Table_Fruits Work.Fruit_Summary I should also be creating the Work.Vegetable_Summary table (see obs1, var9). I am able to create this table if I pass only the first variable of the inputs table to the call execute statement (obs=1). However, it does not get created when I set the entire table (obs=max). My questions are: Does my explanation of this process make sense/is it sufficient? Why isn't the work.vegetable_summary table being created? Is there a better/simpler/more efficient approach to pass a set of inputs to and run the master file loop several times?    I'm hoping this isn't too confusing - if it is, feel free to ask more questions and I will explain as best I can. Thanks for your patience and for your help!    ... View more

I am trying to do the following:   1) Get a list of keys from the base data 2) Get the info from the lookup table for these keys 3) Merge the hash table w/info onto the base table   To do this, I am trying to implement a hash in place (I think this is what it is called). The problem I am running into is that this doesn't work quickly if the lookup table is very large. In this example, imagine if "have_lookup_table" was 5M records long. I'm setting that entire table just to get the lookup info for 4 records, even if those four records are the first four records of the 5M record dataset. Is there a way to get the desired result without having to set the entire table?   data have_base_table; infile datalines delimiter=','; input Item:$8. Trans_Date:yymmdd10. Code :$4.; format Trans_Date yymmdd10.; datalines; Apple, 2020-07-29, 4036 Orange, 2020-07-29, 4031 Carrot, 2020-07-21, 4110 Banana, 2020-07-31, 5191 ; data have_lookup_table; infile datalines delimiter=','; input Item $ Price 8.; datalines; Apple, 3.00 Orange, 1.00 Carrot, 0.50 Banana, 0.75 ; data want; infile datalines delimiter=','; input Item:$8. Trans_Date:yymmdd10. Code :$4. Price:8.; format Trans_Date yymmdd10.; datalines; Apple, 2020-07-29, 4036, 3.00 Orange, 2020-07-29, 4031, 1.00 Carrot, 2020-07-21, 4110, 0.50 Banana, 2020-07-31, 5191, 0.75 ; run; data test; if 0 then set have_lookup_table; if _n_=1 then do; *Define empty hash table to be filled with only the necessary lookups, depending on what is in the base table; dcl hash h(); h.definekey("Item"); h.definedata("Item","Price"); h.definedone(); end; *Fill empty hash table with necessary keys from base table; do until (eof); set have_base_table end=eof; rc_ref=h.ref(); end; h.output(dataset:"hash_keys"); *Fill hash table with corresponding key-matched data; do until (last2); set have_lookup_table end=last2; rc_replace=h.replace(); end; h.output(dataset:"hash_keys_and_data"); call missing(Price); *Merge finished hash table onto base data; do until (last); set have_base_table end=last; rc_h=h.find(); output; end; run; ... View more