topic Re: dictionary.columns How Do I Create A New Column? in SAS Procedures

dictionary.columns How Do I Create A New Column?

JaySwan — Thu, 28 Jul 2011 22:45:15 GMT

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

dictionary.columns How Do I Create A New Column?

art297 — Thu, 28 Jul 2011 22:59:27 GMT

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*/

dictionary.columns How Do I Create A New Column?

JaySwan — Thu, 28 Jul 2011 23:08:09 GMT

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

dictionary.columns How Do I Create A New Column?

art297 — Thu, 28 Jul 2011 23:36:15 GMT

The dictionary only shows what is in your files. Did you run it on the new dataset that you created?

Re: dictionary.columns How Do I Create A New Column?

Tom — Thu, 28 Jul 2011 23:44:04 GMT

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.

Re: dictionary.columns How Do I Create A New Column?

JaySwan — Fri, 29 Jul 2011 22:47:10 GMT

Tom, Thank you very much for this solution...

Learn something new every day!

I'll let me team know..

Thanks, Jay