how to write macro for a variable_metadata sheet ?

[u52766097]

how will i do this => from the spec sheet/metadata file and  all attributes from it and
then at run time assign the attributes in each program using a macro.
proc import -> subset for a dataset -> create attrib statement from that dataset.

[Patrick]

@u52766097 Pretty much along the line how you describe it. I guess you ask because you're not sure how to do below bit.

The code is self-contained and you can run it by copy/pasting into your environment.

data dd;
  infile datalines4 dsd dlm=',' truncover;
  input name :$32. type :$4. length :$5. format :$32. informat :$32. label :$256.;
  datalines4;
colA,char,20,
colB,num,8,datetime21.,,Column B
;;;;

filename codegen temp;
data _null_;
  file codegen;
  set dd;
  put 'attrib ' name @;
  if type='char' then put 'length=$' length @;
  else put 'length=' length @;
  if not missing(format) then put 'format=' format @;
  if not missing(informat) then put 'informat=' informat @;
  /* with labels eventually a bit more logic required in case you need to expect quotes */
  /* and/or macro tokens liek & in the text */
  if not missing(label) then put 'label="' label +(-1) '"' @;
  put ';';
run;

data want;
  %include codegen /source2;
run;

If you need more elaborate code then get yourself inspired by Jedi SAS Tricks: The DATA to DATA Step Macro and the macro linked at the end of the blog.

[Tom · Accepted solution]

No extra logic needed for LABEL attribute. Use the $QUOTE format. 

Also if you name your metadata fields to match the ATTRIB keyword then you can have SAS automatically write the keyword.

if not missing(format) then put format= @;
if not missing(informat) then put informat= @;
if not missing(label) then put label= :$quote. @;

[u52766097]

I tried this method for creating the macro for variables ,so how will i use this for a dataset with attribe statement

%macro adsl;

proc sql noprint;
select memname into:names separated by '|'
from dictionary.tables
where libname eq 'RAW' and memname not in ('DEFINE');
quit;
%put &names;
%let i=1;
%do %while(%scan(&names,&i,|) ne );
%put **********************;
%put **********************;
%put * ;
%put * %scan(&names,&i,|);
%put * ;
%put **********************;
proc sql noprint;
select domain into:dslabel
from rawdata.define
where domain eq "%scan(&names,&i,|)";
select compress(put(count(*),8.)) into:cn
from rawdata.define
where domain eq "%scan(&names,&i,|)";

select variable, label, length
into:column1 - :column&cn,
:label1 - :label&cn,
:vform1 - :vform&cn
from rawdata.define
where domain eq "%scan(&names,&i,|)";
quit;
data derdata.%scan(&names,&i,|) (label="&dslabel" );

set RAW.%scan(&names,&i,|);
%do j=1 %to &cn;
label &&&column&j ="&&&label&j";
format &&&column&j &&&vform&j..;
%end;
run;
%let i=%eval(&i+1);
%end;
%mend adsl;

[Tom]

I never see much value in taking data out of a dataset into macro variables like that.  Just use the method that writes the code to a file. Much easier to understand, read, write and debug.  No messing around with converting number to strings to store into macro variables, no need to use macro logic, worry about macro quoting etc.

Why are you putting values from the LENGTH variable of rawdata.define  into macro variables named VFORM....?

And then why are you using those LENGTH values to generate the FORMAT attribute for the variables? 

Perhaps you are confusing the defined LENGTH of a variable (how much space it takes to store the variable in the dataset) and the display format attached to the variable (how you want the value to display by default).

You also don't seem to be pulling any TYPE information out that metadata table.  Do you only have one type of variable? If so are they floating point numbers or fixed length character strings.  Those are the only two choices for SAS variables.

What about variables that actually need to have formats attached to them (for example DATE, TIME and DATETIME values) to make them user readable? How do you propose attaching a proper format to those?

[u52766097]

Thanks for the response but when i used that code for my DM metadata sheet, it is showing the following NOTE;

NOTE: %INCLUDE (level 1) file codegen is file /saswork/SAS_work7ECC0001CB6C_odaws04-usw2.oda.sas.com/#LN00195.

NOTE: %INCLUDE (level 1) ending.

NOTE: Variable STUDYID is uninitialized.

NOTE: Variable DOMAIN is uninitialized.

NOTE: Variable USUBJID is uninitialized.

NOTE: Variable SUBJID is uninitialized.

NOTE: Variable RFSTDTC is uninitialized.

NOTE: Variable RFENDTC is uninitialized.

NOTE: Variable RFXSTDTC is uninitialized.

NOTE: Variable RFXENDTC is uninitialized.

NOTE: Variable RFICDTC is uninitialized.

NOTE: Variable RFPENDTC is uninitialized.

NOTE: Variable DTHDTC is uninitialized.

NOTE: Variable DTHFL is uninitialized.

NOTE: Variable SITEID is uninitialized.

NOTE: Variable BRTHDTC is uninitialized.

NOTE: Variable AGE is uninitialized.

NOTE: Variable AGEU is uninitialized.

NOTE: Variable SEX is uninitialized.

NOTE: Variable RACE is uninitialized.

NOTE: Variable ARMCD is uninitialized.

NOTE: Variable ARM is uninitialized.

NOTE: Variable ACTARMCD is uninitialized.

NOTE: Variable ACTARM is uninitialized.

NOTE: Variable COUNTRY is uninitialized.

[Tom]

If you ran code like:

data want;
  attrib var1 length=$10;
run;

Then those are expected messages.

What is the source for the actual data?  Do you have some existing dataset?  Are you reading from some text file?

If there is no data then add a STOP statement to insure that no observations are written.

You could use CALL MISSING() to prevent the uninitialized messages.

data want;
  %include codegen /source2;
  call missing(of _all_);
  stop;
run;

[u52766097]

thanks for reply, it worked .