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
Click image to register for webinar
