Thanks for checking. Here is the MPRINT MPRINT(__CODE): proc cas; MPRINT(__CODE): session CASAUTO; MPRINT(__CODE): loadactionset "timeData"; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. NOTE: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. MPRINT(__CODE): action runTimeCode / table={ caslib="CASUSER(swkuma)", name="PRICEDATA" } objOut={ { table={ caslib="CASUSER", name="outStatTemp" , replace="true" }, objRef='outStat' } } seasonality=12 timeId={name='date' , FORMAT="_DATA_"} interval="Month" nlFormat=true series={'sale'} require={{pkg='TSM'}} code=' declare object myModel(TSM); declare object mySpec(ARIMASpec); rc = mySpec.Open(); array ma[2]/nosymbols; ma[1] = 1; ma[2] = 2; rc = mySpec.AddMAPoly(ma); rc = mySpec.SetOption("noint",1); rc = mySpec.SetOption("method","CLS"); rc = mySpec.close(); rc = myModel.Initialize(mySpec); rc = myModel.SetY(sale); rc = myModel.SetOption('lead', 12); rc = myModel.SetOption("alpha",0.05); rc = myModel.Run(); declare object outStat(TSMSTAT); rc = outStat.Collect(myModel); '; ERROR: A name token was expected ERROR: action runTimeCode / table = { caslib = 'CASUSER(swkuma)' , n ERROR: ame = 'PRICEDATA' } objOut = { { table = { caslib = 'CASUSER' ERROR: , name = 'outStatTemp' , replace = 'true' } , objRef = 'outS ERROR: tat' } } seasonality = 12 timeId = { name = 'date' , FORMAT = ERROR: '_DATA_' } interval = 'Month' nlFormat = TRUE series = { 'sa ERROR: le' } require = { { pkg = 'TSM' } } code = ' decla ERROR: re object myModel(TSM); declare object mySpec(ARIM ERROR: ASpec); rc = mySpec.Open(); array ma[2] ERROR: /nosymbols; ma[1] = 1; ma[2] = ERROR: 2; rc = mySpec.AddMAPoly(ma); rc = mySp ERROR: ec.SetOption("noint",1); rc = mySpec.SetOption("me ERROR: thod","CLS"); rc = mySpec.close(); ERROR: rc = myModel.Initialize(mySpec); ERROR: rc = myModel.SetY(sale); rc = myModel.SetOption ERROR: (' lead ', 12); rc = myModel.SetOption("alpha",0.0 ERROR: 5); rc = myModel.Run(); dec ERROR: lare object outStat(TSMSTAT); rc = outStat.Collect ERROR: (myModel); ' ; ERROR: ^ NOTE: The submitted statements have been canceled. MPRINT(__CODE): run; MPRINT(__CODE): ; NOTE: PROCEDURE CAS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more

this is a part of migration activity . I cant give much detail .. i tried using %let to store lines as text , but due to single ' getting issue ... View more

woww! awesome this is what I was looking . thank you ... View more

sorry I should have explained a bit more. I got solution. thanks for checking ... View more

Thanks for checking. yes 3 is coming from a macro variable . I got solution for this problem ... View more

Sorry , t1 t1.property_id is not having this column.. My idea was to add t3.property_value as promo_support, t3.property_value as vehicle_name, t3.property_value as levelname, add these column, but the number of column may change based on prop_Cd ... View more

code without macro proc sql; create table veh_det as select * from &planning_lib..SAS_member where internal_dimension_id = -17 and internal_dim_type_id = -20; quit; proc sql; select distinct property_id into:prop_id separated by ',' from &planning_lib..sas_member_property where internal_dimension_id = -17; quit; %put property_id : &prop_id ; proc sql; select distinct property_cd into:prop_cd separated by ' ' from &planning_lib..sas_property where property_id in (&prop_id); quit; %put &prop_cd; proc sql; create table veh_member as select t1.member_cd as member_id, t2.member_nm, t2.member_desc, t3.property_value as promo_support, t3.property_value as vehicle_name, t3.property_value as levelname, t2.valid_from_dttm, t2.valid_to_dttm from veh_det t1 join &planning_lib..SAS_MEMBER_NLS t2 on t1.member_id = t2.member_id join &planning_lib..SAS_MEMBER_PROPERTY t3 on t1.member_id = t3.member_id and property_id in (&prop_id); quit; log /; 79 80 proc sql; 81 create table veh_det as 82 select * 83 from &planning_lib..SAS_member 84 where internal_dimension_id = -17 85 and internal_dim_type_id = -20; NOTE: Table WORK.VEH_DET created, with 12 rows and 10 columns. 86 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.03 seconds 87 88 proc sql; 89 select distinct property_id into:prop_id separated by ',' 90 from &planning_lib..sas_member_property 91 where internal_dimension_id = -17; 92 quit; NOTE: The PROCEDURE SQL printed page 9. NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.02 seconds 93 94 %put property_id : &prop_id ; property_id : 27,31,32 95 96 proc sql; 97 select distinct property_cd into:prop_cd separated by ' ' 98 from &planning_lib..sas_property 99 where property_id in (&prop_id); 100 quit; NOTE: The PROCEDURE SQL printed page 10. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds 101 102 %put &prop_cd; PROMO_SUPPORT VEHICLE_TYPE LEVELNAME 103 104 proc sql; 105 create table veh_member as 106 select 107 t1.member_cd as member_id, 108 t2.member_nm, 109 t2.member_desc, 110 t3.property_value as promo_support, 111 t3.property_value as vehicle_type, 112 t3.property_value as levelname, 113 t2.valid_from_dttm, 114 t2.valid_to_dttm 115 from 116 veh_det t1 117 join &planning_lib..SAS_MEMBER_NLS t2 118 on 119 t1.member_id = t2.member_id 120 join &planning_lib..SAS_MEMBER_PROPERTY t3 121 on 122 t1.member_id = t3.member_id and property_id in (&prop_id); NOTE: Table WORK.VEH_MEMBER created, with 35 rows and 8 columns. 123 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.13 seconds cpu time 0.11 seconds 124 125 126 /* region: Generated postamble */ 127 /* Close ODS destinations */ 128 &graphterm; ;*';*";*/;run;quit; 129 quit;run; 130 ods html5 (id=web) close; 131 ods listing close; 132 %if %sysfunc(fileref(_gsfname)) lt 0 %then %do; 133 filename _gsfname clear; NOTE: Fileref _GSFNAME has been deassigned. 134 %end; 135 %studio_capture_custom_output; 136 /* endregion */ 137 here from 12records in veh_detail  is converted to  having 36 records in veh_member . its 12* 3 = 36 as 3 columns is getting printed ... View more

This is working , but as I said inside this do loop I have other do loops where I am checking for date_sk.. that portion should iterate also as this Value do loop. ... View more

proc sql noprint; create table &abt_worklib..input_plan_rules_1 as select trim(lowcase(a.pricing_plan_id)) as pricing_plan_id length=37, trim(lowcase(a.business_category_id)) as business_category_id label='business_category_id' length=37, &gv_current_dt. as current_date format=&gv_rd_date_sas_format. label='current_date', %if %length(&init_rec_promo_dt) eq 0 %then %do; . as initial_rec_date_promo format=&gv_rd_date_sas_format. label='initial_rec_date_promo', %end; %else %do; &init_rec_promo_dt. as initial_rec_date_promo format=&gv_rd_date_sas_format. label='initial_rec_date_promo', %end; %if %length(&last_rec_dt_promo) eq 0 %then %do; . as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo', %end; %else %do; &last_rec_dt_promo. as last_rec_date_promo format=&gv_rd_date_sas_format. label='last_rec_date_promo', %end; /*2 as opt_flag format=1. length=3,*/ &opt_flag. as opt_flag format=1. length=3, '' as promo_period length=500 informat=$500. format=$500., case when input(b.rule_val_1525,1.) = 0 then 5 when input(b.rule_val_1525,1.) = 1 then 4 when input(b.rule_val_1525,1.) = 2 then 1 else 0 end as obj_func_promo format=3. length=3, input(b.rule_val_1404,1.) as promo_price_grid_type format=1. length=3, input(b.rule_val_1301,1.) as is_inventory_constrained format=1. length=3, input(b.rule_val_1302,best9.) as min_discount_pct_promo format=&gv_rd_pct_sas_format., input(b.rule_val_1303,best9.) as max_discount_pct_promo format=&gv_rd_pct_sas_format., case when input(b.rule_val_1306,1.) = 1 then input(b.rule_val_1304,best9.) else . end as min_margin_pct_promo format=&gv_rd_pct_sas_format., case when input(b.rule_val_1306,1.) = 2 then input(b.rule_val_1304,best18.) else . end as min_margin_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1306,1.) = 1 then input(b.rule_val_1305,best9.) else . end as max_margin_pct_promo format=&gv_rd_pct_sas_format., case when input(b.rule_val_1306,1.) = 2 then input(b.rule_val_1305,best18.) else . end as max_margin_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1526,1.) = 1 then input(b.rule_val_1503,best18.) else . end as gross_margin_lb_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1526,1.) = 1 then input(b.rule_val_1504,best18.) else . end as gross_margin_ub_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1526,1.) = 0 or input(b.rule_val_1526,1.) = . then . else . end as gross_margin_lb_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1526,1.) = 0 or input(b.rule_val_1526,1.) = . then . else . end as gross_margin_ub_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1527,1.) = 1 then input(b.rule_val_1505,best18.) else . end as sell_through_units_lb_promo format=&gv_rd_qty_sas_format., case when input(b.rule_val_1527,1.) = 1 then input(b.rule_val_1506,best18.) else . end as sell_through_units_ub_promo format=&gv_rd_qty_sas_format., case when input(b.rule_val_1527,1.) = 0 or input(b.rule_val_1527,1.) = . then . else . end as sell_through_units_lb_promo format=&gv_rd_qty_sas_format., case when input(b.rule_val_1527,1.) = 0 or input(b.rule_val_1526,1.) = . then . else . end as sell_through_units_ub_promo format=&gv_rd_qty_sas_format., case when input(b.rule_val_1528,1.) = 1 then input(b.rule_val_1501,best18.) else . end as gross_margin_lb_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1528,1.) = 1 then input(b.rule_val_1502,best18.) else . end as gross_margin_ub_promo format=&gv_rd_amt_sas_format., case when input(b.rule_val_1528,1.) = 2 then input(b.rule_val_1507,best18.) else . end as gross_margin_pct_lb_promo format=&gv_rd_qty_sas_format., case when input(b.rule_val_1528,1.) = 2 then input(b.rule_val_1508,best18.) else . end as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format., case when input(b.rule_val_1528,1.) = 0 or input(b.rule_val_1528,1.) = . then . else . end as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format., 1 as allow_manual_mkd format=1. length=3, 1 as allow_manual_promo_on_reg_price format=1. length=3, 1 as allow_stacked_promo_on_mkd format=1. length=3, 1 as allow_stacked_promo_on_promo format=1. length=3, 1 as allow_promo_price_below_mpp format=1. length=3, input(b.rule_val_1508,best9.) as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format. /*input(b.rule_val_516,6.) as min_periods_since_avail_dt format=&gv_rd_cnt_sas_format.*/ from &abt_worklib..pricing_plan a left join &abt_worklib..plan_rule_transposed b on upcase(a.pricing_plan_id) = upcase(b.pricing_plan_id) left join &pricing_lib..date_dm e on a.end_dt between e.fiscal_start_dt and e.fiscal_end_dt and e.date_lvl_no = &time_low_lvl. ; quit; ... View more