Date:         Mon, 7 Jul 2003 09:10:56 +0100
Reply-To:     Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peter Crawford <Peter@CRAWFORDSOFTWARE.DEMON.CO.UK>
Organization: Crawford Software Consultancy Limited
Subject:      Re: Retrieving labels from a second dataset

Looks like Steve M <sasmark2003@yahoo.com> has embryonic MetaData !

%let ds1 = <name of dataset with column_name and display_name>;
%let ds2 = <name of dataset with the actual data>;

Here are example code to compare two methods of generating those
display_names as the labels for the columns.
Both methods assume valid MetaData, having only correct column
names, and that the column names conform to the sas system
  option   validVarName = v7 ; * i.e. names need not be quoted ;

* extract libname and memname from ds2;
 option _last_ = &ds2;
 %let   ds2lib = %scan( &syslast,1, . );
 %let   ds2mem = %scan( &syslast,2, . );


*With sql, this method prepares a macro var of labels to use in
                       proc datasets;
 %let labelling = ; /* the macro var holder for label assignments */
proc sql noprint;
  select        trim(  column_name ) !! '= ' !!
          quote(trim( display name ))
    into :labelling  separated by '   '
    from &ds1
   ;
quit;
proc datasets library = &ds2lib nolist ;
     modify             &ds2mem    ;
            label       &labelling ;
  run;
quit;


* The second method uses call execute in a datastep, to generate
     the code, to set those labels in proc datasets ;
data _null_;
  call execute( "proc datasets library= &ds2lib  nolist ;");
  call execute( "    modify             &ds2mem ;  label ");
  do until(        end_ds1 ) ;
     set &ds1 end= end_ds1 ;
     call execute( column_name !!'= '!! quote( trim( display_name)) );
  end;
  call execute(  "  ;   /*semicolon ends the label statement
             */  run;   /*run       ends the modify group
             */ quit;   /*quit      ends proc datasets      */ " );
  stop;
run;


Which of these methods do you prefer ?


Regards
Peter Crawford



Steve M <sasmark2003@yahoo.com> writes
>I have two datasets:
>
>the first contains the following lookup data
>
>column_name    display_name
>
>staff_id               Staff Id
>birth_date             Birth Date
>
>The second dataset contains the actual data eg
>
>staff_id               birth_date
>
>1234                   05.05.80
>5678                   10.10.65
>
>Is it possible to reference the first table in order to obtain the
>user-friendly display_name as a label for the second dataset?
>
>I have got as far as retrieving the column names & number of columns
>in order to loop through the cols in dataset 2 but am stuck on how to
>actually find the matching display_name in dataset 1.
>
>Any help gratefully received.

--
Peter Crawford

available for SAS consultancy contracts