run a code five times

[Alexxxxxxx]

Dear all,

I am trying to match HRM_L2(a company name variable) with five different company names(i.e., NAME, COMPANY_NAME, COMPANY_NAME_1, COMPANY_NAME___SHORT, PREVIOUS_NAME) which recorded in the same datasets. 

proc sql;
create table Sa_step4.Datastream_Patstat1 as 
select distinct 
Pa.HRM_L2,
Type
from Step1.Patstat_gb_hrm_set as Pa
join Dataset.Datastream_gb_full as Da on Da.name=Pa.HRM_L2
order by HRM_L2
;
quit;

proc sql;
create table Sa_step4.Datastream_Patstat2 as 
select distinct 
Pa.HRM_L2,
Type
from Step1.Patstat_gb_hrm_set as Pa
join Dataset.Datastream_gb_full as Da on Da.COMPANY_NAME=Pa.HRM_L2
order by HRM_L2
;
quit;


proc sql;
create table Sa_step4.Datastream_Patstat3 as 
select distinct 
Pa.HRM_L2,
Type
from Step1.Patstat_gb_hrm_set as Pa
join Dataset.Datastream_gb_full as Da on Da.COMPANY_NAME_1=Pa.HRM_L2
order by HRM_L2
;
quit;

how can I repeat this code five times? could you please give me some suggestion about this ?

thanks in advance

[VDD]

macro doit(myi=,myvar=);
proc sql;
create table Sa_step4.Datastream_Patstat&myi. as 
select distinct 
Pa.HRM_L2,
Type
from Step1.Patstat_gb_hrm_set as Pa
join Dataset.Datastream_gb_full as Da on Da.&myvar.=Pa.HRM_L2
order by HRM_L2
;
quit;

%mend doit;
%doit(myi=1,myvar=name);
%doit(myi=2,myvar=Company_name);
%doit(myi=3,myvar=company_name_1);
%doit(myi=4,myvar=company_name__short);
%doit(myi=5,myvar=previous_name);

[Alexxxxxxx]

Dear VDD

I appreciate your help.

May I ask one more questions? What should I do if I would like to run your code for ten other countries?

For example, US,CN,CH,BR,CA,CL et cl.

thanks for your attention to this matter.

[VDD]

you could us an array with your list (US,CN,CH,BR,CA,CL et cl.) a in do loop and call the macro.