I am trying to add a column to the SAS Dictionary so I can keep track of which field came from which SAS Dataset after a SAS Merge only in the SAS Data Dictionary.
I Do not realy want the value in the SAS Dataset as that is just a waste of space, and very repetative.
All I need is for the new variable (ContribDataSet) value to show up in the SAS Data Dictionary.
DATA IPSC.AgentProfileWInfo(Drop=InAppl InApplInfo);
Length ContribDataSet $32.;
MERGE
AgentProfile(IN=APro)
AgentProfileAddtionInfo(IN=AProInfo);
BY UserID;
InAppl = APro;
InApplInfo = AProInfo;
/* Sorry had typepo in code after all the cutting and pasting to get to the basics */
If APro then ContribDataSet = "AgentProfile";
If AProInfo then ContribDataSet = "AgentProfileAddtionInfo";
If APro and AproInfo then output IPSC.AgentProfileWInfo;
Run;
proc sql noprint;
create table IPSU.DDAgentProfileWInfo as
select *
/* I want to get the ContribDataSet To Show Up In The Data Dictionary */
from dictionary.columns
where
libname = "IPSC" and memname = "AGENTPROFILEWINFO";
;
quit;
Something like below.
Need to add ContribDataSet column to the dictionary.
libname | memname | memtype | name | ContribDataSet |
IPSC | AGENTPROFILEWINFO | DATA | FirstName | AgentProfile |
IPSC | AGENTPROFILEWINFO | DATA | LastName | AgentProfile |
IPSC | AGENTPROFILEWINFO | DATA | NoCellPhone | AgentProfileAddtionInfo |
IPSC | AGENTPROFILEWINFO | DATA | NoHomePhone | AgentProfileAddtionInfo |
Thanks in advance, Jay
You cannot ADD anything to dictionary.columns as it is just a view into the metadata about existing datasets.
As I understand this you want to QUERY dictionary.columns and try to figure out which VARIABLES in your output dataset came from which input dataset.
SAS just does basically a UNION of the variable names when it does a merge. So you just need to do a merge of the records from dictionary.columns. You can also create new columns when creating a new dataset so you should pull the information from that one also so that you can flag the variables that do not come from either of the two source datasets. Attributes like LENGTH will be set by the setting from the first dataset, but attributes like FORMAT have a more complex logic for how they are set. Plus your data step could have FORMAT or LABEL statements to change them.
proc sql noprint ;
create table ds1 as select * from dictionary.columns
where libname = "IPSC" and memname = "AGENTPROFILE"
order by name
;
create table ds2 as select * from dictionary.columns
where libname = "IPSC" and memname = "AGENTPROFILEADDITIONALINFO"
order by name
;
create table ds3 as select * from dictionary.columns
where libname = "IPSC" and memname = "AGENTPROFILEWINFO"
order by name
;
quit;
data varlist ;
merge ds1 ds2 ;
by name ;
contribdataset = memname ;
run;
data varlist ;
merge varlist ds3 ;
by name ;
if contribdataset = ' ' then contribdataset=memname;
run;
You could probably figure out how to do this in a single SQL statement.
Your description doesn't provide enough info to tell what you mean. Your step indicated:
AgentProfile(IN=APro)
AgentProfileAddtionInfo(IN=AProInfo);
BY UserID;
then you create:
InAppl = APro;
InApplInfo = AProInfo;
then you try to use:
If Appl then ContribDataSet = "AgentProfile";
If App then ContribDataSet = "AgentProfileAddtionInfo";
If Appl and App then output IPSC.AgentProfileWInfo;
what is App?
If that was just a typing error, I think you probably want your last statement first, and depending upon what you want, a slight change to what you asked. e.g.,
If InAppl and InAppInfro then ContribDataSet='Both';
else If Appl then ContribDataSet = "AgentProfile";
else ContribDataSet = "AgentProfileAddtionInfo";
output IPSC.AgentProfileWInfo; /* although that isn't needed if you are going to output all results*/
Thanks, I did have a typo...
However, the variable ContribDataSet still does not show up when I use "from dictionary.columns" in the Proc SQL.
I only want to output if the UserID is in both datasets...
Is there a way to define new variables to the dictionary?
Thanks for your time..
Jay
The dictionary only shows what is in your files. Did you run it on the new dataset that you created?
You cannot ADD anything to dictionary.columns as it is just a view into the metadata about existing datasets.
As I understand this you want to QUERY dictionary.columns and try to figure out which VARIABLES in your output dataset came from which input dataset.
SAS just does basically a UNION of the variable names when it does a merge. So you just need to do a merge of the records from dictionary.columns. You can also create new columns when creating a new dataset so you should pull the information from that one also so that you can flag the variables that do not come from either of the two source datasets. Attributes like LENGTH will be set by the setting from the first dataset, but attributes like FORMAT have a more complex logic for how they are set. Plus your data step could have FORMAT or LABEL statements to change them.
proc sql noprint ;
create table ds1 as select * from dictionary.columns
where libname = "IPSC" and memname = "AGENTPROFILE"
order by name
;
create table ds2 as select * from dictionary.columns
where libname = "IPSC" and memname = "AGENTPROFILEADDITIONALINFO"
order by name
;
create table ds3 as select * from dictionary.columns
where libname = "IPSC" and memname = "AGENTPROFILEWINFO"
order by name
;
quit;
data varlist ;
merge ds1 ds2 ;
by name ;
contribdataset = memname ;
run;
data varlist ;
merge varlist ds3 ;
by name ;
if contribdataset = ' ' then contribdataset=memname;
run;
You could probably figure out how to do this in a single SQL statement.
Tom, Thank you very much for this solution...
Learn something new every day!
I'll let me team know..
Thanks, Jay
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.