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

Thanks. It still giving me wrong input for columns. for.eg I am getting "missing value" for all columns where case statement is applied in 15** series ... 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., %if input(b.rule_val_1526,1.) = 1 %then %do; input(b.rule_val_1503,best18.) as gross_margin_lb_promo format=&gv_rd_amt_sas_format., input(b.rule_val_1504,best18.) as gross_margin_ub_promo format=&gv_rd_amt_sas_format., %end; %else %if input(b.rule_val_1526,1.) = 0 or input(b.rule_val_1526,1.) = . %then %do; . as gross_margin_lb_promo format=&gv_rd_amt_sas_format., . as gross_margin_ub_promo format=&gv_rd_amt_sas_format., %end; %if input(b.rule_val_1527,1.) = 1 %then %do; input(b.rule_val_1505,best12.) as sell_through_units_lb_promo format=&gv_rd_qty_sas_format., input(b.rule_val_1506,best12.) as sell_through_units_ub_promo format=&gv_rd_qty_sas_format., %end; %else %if input(b.rule_val_1527,1.) = 0 or input(b.rule_val_1527,1.) = . %then %do; . as sell_through_units_lb_promo format=&gv_rd_qty_sas_format., . as sell_through_units_ub_promo format=&gv_rd_qty_sas_format., %end; %if input(b.rule_val_1528,1.) = 1 %then %do; input(b.rule_val_1501,best18.) as gross_margin_lb_promo format=&gv_rd_amt_sas_format., input(b.rule_val_1502,best18.) as gross_margin_ub_promo format=&gv_rd_amt_sas_format., %end; %else %if input(b.rule_val_1528,1.) = 2 %then %do; input(b.rule_val_1507,best9.) as gross_margin_pct_lb_promo format=&gv_rd_qty_sas_format., input(b.rule_val_1508,best9.) as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format., %end; %else %if input(b.rule_val_1528,1.) = 0 or input(b.rule_val_1528,1.) = . %then %do; . as gross_margin_lb_promo format=&gv_rd_amt_sas_format., . as gross_margin_ub_promo format=&gv_rd_amt_sas_format., . as gross_margin_pct_lb_promo format=&gv_rd_qty_sas_format., . as gross_margin_pct_ub_promo format=&gv_rd_qty_sas_format., %end; 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_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; %end; ... View more

Thanks for suggesting . it's not working like that 😞 too ... View more

thanks, it is giving same error %if input(b.rule_val_1526,1.) = 1 %then %do; I think I need to post while proc sql query. I got to know that if condition can't be used inside proc sql. ... View more