Hello,
I have a word document that is given to me that is a mock up of a project I'm going to work on. What I want to do is take the list of tables at the start of the document and then scan the document and pull out and instance where the table.variable combination is listed so I can then create a process to check that these table.variable combinations exist before we start the project.
The reason is that the department we work with gives us mocks that don't match the meta data of their tables and we want to resolve it before we start.
Attached is a test version of how the document will look.
data mock ;
length text $400 ;
infile '[path]\temp.txt' length = l ;
input @1 text $varying400. l ;
run ;
data tables (drop=flag) ;
set mock ;
retain flag ;
if text = 'FILES:' then flag=1 ;
if text = ' ' then flag=0 ;
if text ne 'FILES:' and flag=1 then output ;
run ;
data tables_ (keep=tabname) ;
set tables ;
tabname = scan(strip(text),1) ;
run ;
This code will give me a table with a list of tables to search. I need to have this list in the final table so I can loop through the dictionary.columns table making sure these variables exist in those tables.
tab1.id tab1.type tab1.region tab2.ph1 tab2.Fore1 tab2.Ext1 tab3.ph2 tab3.Fore2 tab3.Ext2 tab4.ph3 tab4.Fore3 tab4.Ext3 tab5.name tab5.co tab5.addr tab5.city tab5.email
Thank you
I am not quite sure I follow.
So is your question how to read that attached files to get the information that is follows the files in such a way that you can have the tab1.id and similar values? And then look those up in dictionary.columns?
To search dictionary.columns we would need a library name.
And does your actual source file have the line numbers preceeding?
This reads the data, gets the likely tablename.variable strings and parses out the memname(table) and name (variable).
data possiblevars ; length text $400 ; infile datalines length = l ; input @1 text $varying400. l ; length str $ 42.; do i=1 to (countw(text,' :,')); str = scan(text,i,' :,'); if index(str,'.')>0 then do; memname=upcase(scan(str,1,'.')); name = upcase(scan(str,2,'.')); output; end; end; drop i text; datalines; PROJECT TEST 1 FILES: tab1 tab2 tab3 tab4 tab5 ID: tab1.id Type: tab1.type REGION: tab1.region Cell: tab2.ph1 tab2.Fore1 tab2.Ext1 Home: tab3.ph2 tab3.Fore2 tab3.Ext2 Other: tab4.ph3 tab4.Fore3 tab4.Ext3 tab5.name tab5.co tab5.addr tab5.city tab5.email ; run;
I am not quite sure I follow.
So is your question how to read that attached files to get the information that is follows the files in such a way that you can have the tab1.id and similar values? And then look those up in dictionary.columns?
To search dictionary.columns we would need a library name.
And does your actual source file have the line numbers preceeding?
This reads the data, gets the likely tablename.variable strings and parses out the memname(table) and name (variable).
data possiblevars ; length text $400 ; infile datalines length = l ; input @1 text $varying400. l ; length str $ 42.; do i=1 to (countw(text,' :,')); str = scan(text,i,' :,'); if index(str,'.')>0 then do; memname=upcase(scan(str,1,'.')); name = upcase(scan(str,2,'.')); output; end; end; drop i text; datalines; PROJECT TEST 1 FILES: tab1 tab2 tab3 tab4 tab5 ID: tab1.id Type: tab1.type REGION: tab1.region Cell: tab2.ph1 tab2.Fore1 tab2.Ext1 Home: tab3.ph2 tab3.Fore2 tab3.Ext2 Other: tab4.ph3 tab4.Fore3 tab4.Ext3 tab5.name tab5.co tab5.addr tab5.city tab5.email ; run;
Hi Ballard,
Thank you so much. Your data steps returns the correct 17 variables. Sorry for any confusion.
I justed wanted to read in that file and get the list of tables which are listed under "FILES" and then using those table names search the reset of the document for each table.variable combination.
So tab1 would be first and I want it to return the n variables that are associated with tab1.
In this example it is these 3 like your code returns.
tab1.id
tab1.type
tab1.region
Thanks again this is a big help.
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.