The middle table comes from the Oracle database that I am using, sorry I forgot to mention that. My task was to devise a program that can take information out of the database and transform it using SAS enterprise guide. My supervisor told me to look specifically at the 'lympost_ds_lym_dis_stat_ct' variable. I was able to complete this task. However the program is written specifically for that variable. Names of tables, joins, the transpose, etc. all relate directly to this variable. My supervisor has now asked me to write code that can do the same transformations for any variable. I could achieve this by re-hardcoding all the names (tables, joins, etc.) in the original program but I have hundreds of variables I potentially need to transform so re-hardcoding everything would be a waste of my time. I am trying to write a program where I can change the value stored in one macro variable and all the other places I would need to re-code have been replaced by the macro variable. Using the middle table (variable_definition) as a part of this process is where I am having problems. Here is what needs to happen: All of my variables are stored in the event_data table. My supervisor tells me to transform a specific variable (lympost_ds_lym_dis_stat_ct). I filter the values in the event_data table so that I am left with just the desired variable as show here: PROC SQL;
CREATE TABLE event_data AS
SELECT * FROM WORK.KVS_EVENT_DATA t1
WHERE t1.KEY LIKE 'LYMPOST_DS_LYM_DIS_STAT_BY_CT%'
ORDER BY PERSON_ID, MAIN_EVENT_ID, EVENT_ID
;
QUIT; And the resulting table: Now I need to join this table with an aggregation table. Since the original program is written for this one specific variable, I did not write code to find the aggregation table. I just went into my Oracle database and found the name of the table I needed through their point and click filter options, which is CD_Lymstaps. This is the table that gave me that information (variable_definition): So from this table it can be seen that for my variable (lympost_ds_lym_dis_stat_ct, second from left), the corresponding aggregation table is CD_Lymstaps (stored under aggregation_order_group, far right). From here, I extract this aggregation table into SAS EG from Oracle using this code: CREATE TABLE CD_LYMSTAPS AS
SELECT * FROM CONNECTION TO dbconn
(SELECT * FROM CIT_CT_STG.CT_VALUE_TRANSLATION
WHERE CDE_TABLE = 'CD_LYMSTAPS');
disconnect from dbconn; Which results in this table (CD_Lymstaps): Now I merge my CD_Lymstaps table and event_data table for further transformation and analysis. My issue is this: I have hundreds of variables I will need to look at. I would like to define one macro variable at the beginning of my code, it could be anything. Lets pretend varname=lympost_ds_lym_dis_stat_ct. Now I need a program that will use varname to extract all values related to lympost_ds_lym_dis_stat_ct, and store it in a table (event_data). Furthermore, I need code that uses varname to access my variable_definition table in Oracle and for my variable (lympost_ds_lym_dis_stat_ct), find the corresponding value under aggregation_order_group, in this case 'CD_Lymstaps' and save this variable in another macro, varname2. So now varname2=CD_Lymstaps. Now for the last part, I need to create a table that has the name of whatever is stored in varname2, and this table contains all the values corresponding to the value stored in varname2, in this case (CD_Lymstaps)
... View more
