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
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);
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.
you could us an array with your list (US,CN,CH,BR,CA,CL et cl.) a in do loop and call the macro.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.