Help using Base SAS procedures

dictionary.columns How Do I Create A New Column?

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

dictionary.columns How Do I Create A New Column?

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.

 

libnamememnamememtypenameContribDataSet
IPSC    AGENTPROFILEWINFO               DATA    FirstName         AgentProfile
IPSC    AGENTPROFILEWINFO               DATA    LastName                 AgentProfile
IPSC    AGENTPROFILEWINFO               DATA    NoCellPhone              AgentProfileAddtionInfo
IPSC    AGENTPROFILEWINFO               DATA    NoHomePhone       AgentProfileAddtionInfo

Thanks in advance, Jay


Accepted Solutions
Solution
‎07-28-2011 07:44 PM
Super User
Super User
Posts: 7,039

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

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.

View solution in original post


All Replies
PROC Star
Posts: 7,467

dictionary.columns How Do I Create A New Column?

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

Contributor
Posts: 24

dictionary.columns How Do I Create A New Column?

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

PROC Star
Posts: 7,467

dictionary.columns How Do I Create A New Column?

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

Solution
‎07-28-2011 07:44 PM
Super User
Super User
Posts: 7,039

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

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.

Contributor
Posts: 24

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

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

Learn something new every day!

I'll let me team know..

Thanks, Jay

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 165 views
  • 0 likes
  • 3 in conversation