%macro assign_attrib(dsnin=, domain=, suppfl=N, spec=);

	 **** read in metadata from excel spec file    *****;
	 proc import datafile="&spec" out=meta_datasets
	 	dbms = xlsx
		replace;
	 	sheet="Datasets";
		datarow=2;
		getnames=no;
	 run;
	 
 	proc import datafile="&spec" out=meta_&domain
	 	dbms = xlsx
		replace;
	 	sheet="&domain";
		datarow=2;
		getnames=yes;
	 run;

	 
	 *** select variables to keep   ***;
	 proc sort data=meta_&domain; 
		by VARIABLE__ORDER; 
		where upcase(variable_use_flag)='Y';
     run;
	 proc sql noprint;
		select variable_name into :_keepvars separated by ' '
			from meta_&domain;

		*** dataset label and sort key   ***;
		select b into :_dsnlbl
			from meta_datasets
			where upcase(a)=upcase("&domain");
		select tranwrd(f, ",",' ') into :_sortkey
			from meta_datasets
			where upcase(a)=upcase("&domain");
	 quit;

	 
	*PN ****************************************************************************;
	*PN  Create macro variables for the variable names, type, labels and length   **;
	*PN ****************************************************************************;

	 data _null_;
		set meta_&domain
            end=eof nobs=nvars;
		
		call symputx('varnam'||compress(put(_n_,3.)),strip(variable_name));
		call symputx('varlbl'||compress(put(_n_,3.)),strip(variable_label));
		call symputx('vartyp'||compress(put(_n_,3.)),strip(type));
		call symputx('varlen'||compress(put(_n_,3.)),strip(length));
		call symputx('varfmt'||compress(put(_n_,3.)),strip(ct_or_formats));
		*PN count the total number of variables;
		if eof then do;
			call symputx('nvars', compress(put(nvars,3.)));
		end;
	 run;

	 
	*** create --seq  ****;
	proc sort data=&dsnin; by &_sortkey; run;
	data &dsnin;
		set &dsnin;
		by &_sortkey;

		if first.usubjid then cmseq=0;
		cmseq + 1;
	run;

	*** output final dataset;
	data sdtmxl.&domain.(label="&_dsnlbl");

	  %do i=1 %to %sysfunc(countw(&_keepvars));
	    %if %upcase(&&vartyp&i) = CHAR %then %do;
	    attrib &&varnam&i  label="&&varlbl&i" /*length=$&&varlen&i..*/;
	    %end;
	    %if %upcase(&&vartyp&i) = NUM %then %do;
	    attrib &&varnam&i  label="&&varlbl&i" /*format=&&varfmt&i length=&&varlen&i..*/;
	    %end;
	  %end;

		set &dsnin;
		by &_sortkey;

		keep &_keepvars;
	run;

	%if &suppfl eq Y %then %do;
		**** read in qnams from spec    *****;
		 proc import datafile="&spec" out=meta_supp
		 	dbms = xlsx
			replace;
		 	sheet="SUPP&domain";
			datarow=2;
		 run;

		 *** select qnams to keep   ***;
		 proc sort data=meta_supp; 
			by QUALIFIER__ORDER; 
			where upcase(variable_use_flag)='Y';
	     run;

		*PN ****************************************************************************;
		*PN  Create macro variables for the variable names, type, labels and length   **;
		*PN ****************************************************************************;
		 data _null_;
			set meta_supp end=eof nobs=nvars;
			
			call symputx('qnam'||compress(put(_n_,3.)),strip(qualifier_name));
			call symputx('qlbl'||compress(put(_n_,3.)),strip(qualifier_label));
			call symputx('qorig'||compress(put(_n_,3.)),strip(qorig));
			call symputx('qeval'||compress(put(_n_,3.)),strip(qeval));

			*PN count the total number of variables;
			if eof then do;
				call symputx('nqnams', compress(put(nvars,3.)));
			end;
		 run;

		 ***  get data type of idvars and qnams   ****;
	     proc contents data=&dsnin out=&domain._contents noprint; run;
	     data _null_;
	         set &domain._contents;
	         call symput("t"||strip(name), strip(put(type, best.)));
	     run;

		 
		data supp&domain;			
			set &dsnin;

			length idvar idvarval $200 qnam $8 qlabel $40 qval qorig qeval $200;
	        idvar="";
	        idvarval="";
	        qorig="";
	        qeval="";

			%do v=1 %to &nqnams;
				if missing(&&qnam&v..)=0 then do;
					qnam=upcase("&&qnam&v");
					qlabel=strip("&&qlbl&v..");
					qval=strip(&&qnam&v..);
					idvar="&domain.SEQ";
					idvarval=strip(put(&domain.seq, best.));
					qorig=dequote(&&qorig&v);
					%if &&qeval&v ne  %then qeval=strip(&&qeval&v));
					output;
				end;
			%end;


			keep studyid domain usubjid idvar idvarval qnam qlabel qval qorig qeval;
	        rename domain=rdomain;
	        label
	            studyid="Study Identifier"
	            domain="Related Domain Abbreviation"
	            usubjid="Unique Subject Identifier"
	            idvar="Identifying Variable"
	            idvarval="Identifying Variable Value"
	            qnam="Qualifier Variable Name"
	            qlabel="Qualifier Variable Label"
	            qval="Data Value"
	            qorig="Origin"
	            qeval="Evaluator"
	        ;
		run;
	
		*** output final dataset;
		proc sort data=supp&domain out=sdtmxl.supp&domain(label="Supplemental Qualifiers for &domain");
			by studyid rdomain usubjid idvar idvarval qnam;
		run;

	%end;

 %mend assign_attrib;


 %assign_attrib(dsnin=fin, 
                domain=CM,
				suppfl=Y,
                spec=../docs/specs/sdtm/&sdtmspec..xlsx);
                
                
                
                
****  adam     *****;
%macro assign_attrib(dsnin=, domain=, spec=);

	** make a local copy incase file is locked    *****;
	%put &spec;
	%if "&ahome" = "/u1/stat" %then %do;
		x "cp &spec temp_delete.xlsx";
	%end;
	%else %do;
		option noxwait;
		x "copy &spec temp_delete.xlsx";
	%end;

	%macro readin_excel(sheet=, dout=);
	 **** read in metadata from excel spec file    *****;
	 proc import datafile="temp_delete.xlsx" out=&dout
		dbms = xlsx replace;
		sheet="&sheet";
	 run;
	 %mend readin_excel;

	 %readin_excel(sheet=adsl, dout=meta_core);
	 %readin_excel(sheet=&domain, dout=meta_&domain);
	 %readin_excel(sheet=Metadata, dout=meta2_&domain);

	%if "&ahome" = "/u1/stat" %then %do;
		x "rm temp_delete.xlsx";
	%end;
	%else %do;
		option noxwait;
		x "del temp_delete.xlsx";
	%end;

	 *** select variables to keep   ***;
	data meta_&domain;
		set meta_&domain;
		where upcase(data_set)=upcase("&domain") and variable ne 'ADSL_CORE';
	run;

	 proc sql noprint;
		select variable into :_keepvars separated by ' '
			from meta_&domain
		;
		select variable into :_adslvars separated by ' '
			from meta_core
			where upcase(core_variable) = 'Y';
		/*select variable into :_domainvars separated by ' '
			from meta_&domain
			where upcase(data_set)=upcase("&domain") and upcase(source) ne "ADSLXL";*/

		*** dataset label and sort key   ***;
		select Data_Set_Label, tranwrd(KEY_VARIABLES_OF_DATASET, ",",' ')
			into :_dsnlbl, :_sortkey
			from meta2_&domain
			where upcase(Dataset___Name)=upcase("&domain");
			/*
		select tranwrd(Key___Variables_of_Dataset, ",",' ') into :_sortkey
			from meta2_&domain
			where upcase(Dataset___Name)=upcase("&domain");*/

	 quit;

	 ***** create ASEQ    *****;
	 proc sort data=&dsnin; by &_sortkey; run;
	 data &dsnin;
	 	set &dsnin;
		by &_sortkey; 
		if first.usubjid then aseq=1;
		else aseq+1;
	 run;



	*PN ****************************************************************************;
	*PN  Create macro variables for the variable names, type, labels and length   **;
	*PN ****************************************************************************;

	 data _null_;
		set meta_&domain
			end=eof nobs=nvars;

		call symputx('varnam'||compress(put(_n_,3.)),strip(variable));
		call symputx('varlbl'||compress(put(_n_,3.)),strip(label));
		call symputx('vartyp'||compress(put(_n_,3.)),strip(type));
		call symputx('varlen'||compress(put(_n_,3.)),strip(length));
		call symputx('varfmt'||compress(put(_n_,3.)),strip(format));
		*PN count the total number of variables;
		if eof then do;
			call symputx('nvars', compress(put(nvars,3.)));
		end;
	 run;


	data &domain._only;
	  %do i=1 %to %sysfunc(countw(&_keepvars));
		%if %upcase(&&vartyp&i) = CHAR %then %do;
		attrib &&varnam&i  label="&&varlbl&i" /*length=$&&varlen&i..*/;
		%end;
		%if %upcase(&&vartyp&i) = NUM %then %do;
		attrib &&varnam&i  label="&&varlbl&i" format=&&varfmt&i /*length=&&varlen&i..*/;
		%end;
	  %end;
	  set &dsnin;

	  keep &_keepvars;
	run;

	*** merge with adsl  ***;
	proc sort data=anadata.adsl out=_adsl(keep=&_adslvars); by studyid usubjid trtsdt; run;
	proc sort data=&domain._only; by studyid usubjid; run;
	data &domain;
		merge _adsl(in=a) &domain._only(in=b);
		by studyid usubjid;
		if a and b;
	run;

	*** output final dataset;
	proc sort data=&domain out=anaxl.&domain._qc(label="&_dsnlbl");
		by &_sortkey;
	run;

 %mend assign_attrib;


 %assign_attrib(dsnin=fin0,
				domain=adex,
				spec=%str(%"../docs/specs/adam/&adamspec..xlsx%"));