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.
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.
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.
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.
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.
And another question: do you need the result as dataset or as report?
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;
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.
Thank you @andreas_lds @novinosrin !!!! This is working perfectly with my code. Really appreciate you taking the time to help.
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?
@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:
Maybe re-posting that question as new thread would be a good idea, because many people ignore already answered topics.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.