topic Translate one dataset to another using a custom metadata dataset in SAS Programming

Translate one dataset to another using a custom metadata dataset

Sha88 — Thu, 29 Aug 2019 11:46:18 GMT

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.

Re: Translate one dataset to another using a custom metadata dataset

KachiM — Thu, 29 Aug 2019 12:02:59 GMT

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.

Re: Translate one dataset to another using a custom metadata dataset

andreas_lds — Thu, 29 Aug 2019 12:06:36 GMT

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.

Re: Translate one dataset to another using a custom metadata dataset

Sha88 — Thu, 29 Aug 2019 12:12:13 GMT

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.

Re: Translate one dataset to another using a custom metadata dataset

andreas_lds — Thu, 29 Aug 2019 12:18:32 GMT

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

Re: Translate one dataset to another using a custom metadata dataset

novinosrin — Thu, 29 Aug 2019 12:24:23 GMT

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;

Re: Translate one dataset to another using a custom metadata dataset

andreas_lds — Thu, 29 Aug 2019 12:37:15 GMT

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.

Re: Translate one dataset to another using a custom metadata dataset

Sha88 — Thu, 29 Aug 2019 14:00:28 GMT

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

Re: Translate one dataset to another using a custom metadata dataset

Sha88 — Thu, 29 Aug 2019 14:04:36 GMT

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?

Re: Translate one dataset to another using a custom metadata dataset

andreas_lds — Fri, 30 Aug 2019 04:29:33 GMT

@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.