BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JaySwan
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
art297
Opal | Level 21

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

JaySwan
Calcite | Level 5

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

art297
Opal | Level 21

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

Tom
Super User Tom
Super User

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.

JaySwan
Calcite | Level 5

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

Learn something new every day!

I'll let me team know..

Thanks, Jay

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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