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

Hi,

outfile and outfile1 have many variables with Scenario_ID the first variable respectively.  So it is impractical to list all of them. I would like to select all the variables from these two files but without the duplicate Scenario_ID column.

How to do this without a warning message? The following code will cause a warning message of

 

WARNING: Variable Scenario_ID already exists on file outfile:

proc sql;

  create table outfile2 as

  select a.*, b.*

  from outfile1 as a right join outfile as b

  on a.Scenario_ID=b.Scenario_ID;

quit;

How to let Scenario_ID be the first variable in outfile2 without duplicate Scenario_ID warning sign?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Change the name of one of them.

proc sql;

  create table outfile2(drop=xxx) as

    select a.*, b.*

    from outfile1 as a

         left join

         outfile(rename=(Scenerio_Id=xxx)) as b

    on a.Scenario_ID=b.xxx

  ;

quit;

View solution in original post

4 REPLIES 4
ballardw
Super User

Explicitly list the variables from b and leave out b.Scenario_ID to remove the warning.

Or live with the warning, it is only reminding you that both data sets contributed. Since they have the same value due to your join there isn't a problem. The warning is for other variables that you don't address, for example a.name and b.name, you may not get the one you want in all cases.

Tom
Super User Tom
Super User

Change the name of one of them.

proc sql;

  create table outfile2(drop=xxx) as

    select a.*, b.*

    from outfile1 as a

         left join

         outfile(rename=(Scenerio_Id=xxx)) as b

    on a.Scenario_ID=b.xxx

  ;

quit;

Macro
Obsidian | Level 7

Tom, what if there is no create table statement in the above code and only select statement exists. In that case, how to efficiently drop some of variables in the condition statement in output result assuming there are many variables in a and b?

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1710 views
  • 3 likes
  • 3 in conversation