Translate one dataset to another using a custom metadata dataset

[Sha88]

Hi, I am fairly new to SAS and have been using it for ETL purposes. 

I have a source table, which I need to export to different people. Each time I need to output different columns and change the variable names, but the source table structure remains the same. 

I want to do this by having static code and creating a metadata table in Excel, which I can change before I run the program each time and this tell SAS which variables to keep and what they are called..  Is this possible? 

E.g  My Source Table:

S_Name	S_ID	S_Date	S_Location	S_Region	S_Comments	S_Tel
Bill	1	01-Jan-2019	UK	EMEA	None	+44 20 999 999
Adam	2	01-Feb-2019	UK	EMEA	Closing	+44 20 999 998
Sean	3	01-Mar-2019	ES	EMEA	Open	+34 01 889 887
Mary	4	01-Apr-2019	US	APAC	Open	+1 555 9901

My Metadata Table:

Variable	Order	Source
Name	1	S_Name
ID	2	S_ID
Region	3	S_Region

My Desired Output:

Name	ID	Region
Bill	1	EMEA
Adam	2	EMEA
Sean	3	EMEA
Mary	4	APAC

I was playing around with loops and adding the new variables to the source table, then using the KEEP to store only what I need but can’t seem to get it just right.  

Any advice would be really appreciated.  Thanks.

[KachiM]

You are having SOURCE as a SAS Data Set.

Your metadata can be held as a macro var, say STR.

%let STR = 'S_Name S_ID S_Region';

Then Proc Print SOURCE with KEEP  = &STR statement.

No need for Excel metadata.

[Sha88]

Hi,  I want to use Excel.   So I can change the columns without changing code and the main priority is to have the variable names change in the output.  I need variable name to change from S_Name to Name for example.    Then the next time it could be First_Name.

[andreas_lds]

Yes, this can be done, but i would not save the metadata in an excel file, but in a text-file, so that all variables have the expected type and length after reading it.

Please provide data in usable form: data-steps using datalines statements making it easier to work on solving the problem, instead of re-creating the necessary data.

[andreas_lds]

And another question: do you need the result as dataset or as report?

[novinosrin]

data meta;
input (Variable	Order	Source)(:$32.);
cards;
Name	1	S_Name
ID	2	S_ID
Region	3	S_Region
;
data source;
input (S_Name 	S_ID	S_Date	S_Location	S_Region	S_Comments	S_Te) (& $32.);
cards;
Bill	1	1-Jan-19	UK	EMEA	None	 +44 20 999 999
Adam	2	1-Feb-19	UK	EMEA	Closing	 +44 20 999 998
Sean	3	1-Mar-19	ES	EMEA	Open	 +34 01 889 887
Mary	4	1-Apr-19	US	APAC	Open	 +1 555 9901
;
data _null_;
set meta end=z;
retain keep;
length keep $100;
keep=catx(' ',keep,variable);
if _n_=1 then call execute('data want;set source;');
call execute(catx('=',variable,source)||';');
if z then call execute(catx(' ','keep  ',keep,';','run;'));
run;

[andreas_lds · Accepted solution]

Here's an attempt - in some parts like the one already posted by @novinosrin 

data _null_;
   set work.Metadata end=jobDone;

   length
      RetainList KeepList $ 350
      RenameList $ 700
   ;

   retain RetainList KeepList RenameList;

   RetainList = catx(' ', RetainList, Variable);
   KeepList = catx(' ', KeepList, Source);
   RenameList = catx(' ', RenameList, Source, '=', Variable);

   if jobDone then do;
      call execute('data target;');
      call execute(catx(' ', 'retain', RetainList, ';'));
      call execute(catx(' ', 'set Source(keep=', KeepList, 'rename=(', RenameList, '));'));
      call execute('run;');
   end;
run;

i am still thinking about a way to skip reading/writing all observations. But i don't think this is possible at all, because of the requested re-ordering of variables.

[Sha88]

Thank you @andreas_lds  @novinosrin  !!!! This is working perfectly with my code.    Really appreciate you taking the time to help.  

[Sha88]

I don't want to ask too much, but do you think it would be possible to have variable length in the metadata table and have this set in the target? 

[andreas_lds]

---

@Sha88 wrote:

I don't want to ask too much, but do you think it would be possible to have variable length in the metadata table and have this set in the target? 

---

For alphanumeric variables only? That would make the whole process a lot more complex:

• a length-statement has be generated, probably replacing the retain statement
• you need to compare the metadata of source with the metadata table to decide if and what statement you have to generate for each variable.
• if the original value needs to be shortened, a call to substr is necessary.

Maybe re-posting that question as new thread would be a good idea, because many people ignore already answered topics.