%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);