DATA Step, Macro, Functions and more

use proc sql to rewrite code with "data set"

Accepted Solution Solved
Reply
Super Contributor
Posts: 345
Accepted Solution

use proc sql to rewrite code with "data set"

I want to merge two datasets. I previously needed to sort them first, them

data want

set have1 have2

indsname=ind

contributing_tbl=ind;

run;

 

In this way, I get a new variable indicating where it is originally from

 

However, use "set" is too slow and needs to sort first, which takes more time, I want to change to use proc sql;

however, I need advice from you how to have a simiar statement that adds a column that indicting where it is orginally from have1, or have2,

would you please advice? Thanks.


Accepted Solutions
Solution
‎08-11-2016 11:18 AM
Super User
Super User
Posts: 7,988

Re: use proc sql to rewrite code with "data set"

As mentioned by @Doc_Duke, you are not sorting the data nor merging it.  The set statement in the given example will take all the data from table1, then below that will set all the data from table2.  You can use the proc append procedure to achieve the same thing (and it would be quicker):

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000070936.htm

 

But if your fixed on SQL, you won't get much speed gains, you would use union command - note columns have to all be the same:

 

proc sql;
  create table WANT as
  select   *
  from     (select *,"From Table1" as INTBL from TABLE1) 
  union all
  select   *
  from     (select *,"From Table2" as INTBL from TABLE2);
quit;

 

 

View solution in original post


All Replies
Trusted Advisor
Posts: 2,116

Re: use proc sql to rewrite code with "data set"

The syntax you have is not technically correct (missing multiple semi-colons), so it is hard to understand what you are really trying to do.  The DATA step, as stated, does not require the data sets to be sorted; is there a BY statement missing?

Solution
‎08-11-2016 11:18 AM
Super User
Super User
Posts: 7,988

Re: use proc sql to rewrite code with "data set"

As mentioned by @Doc_Duke, you are not sorting the data nor merging it.  The set statement in the given example will take all the data from table1, then below that will set all the data from table2.  You can use the proc append procedure to achieve the same thing (and it would be quicker):

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000070936.htm

 

But if your fixed on SQL, you won't get much speed gains, you would use union command - note columns have to all be the same:

 

proc sql;
  create table WANT as
  select   *
  from     (select *,"From Table1" as INTBL from TABLE1) 
  union all
  select   *
  from     (select *,"From Table2" as INTBL from TABLE2);
quit;

 

 

Super Contributor
Posts: 345

Re: use proc sql to rewrite code with "data set"

Thank you!

Super Contributor
Posts: 345

Re: use proc sql to rewrite code with "data set"

My actual problem is,

combine first two into class I; then combine the last two into class II; then combine these two classes into one big dataset;

can you give advice how to do it with one proc sql step?

 

Thanks

Super User
Posts: 5,437

Re: use proc sql to rewrite code with "data set"

You are confusing merge with append...?
Your code does append data and then there is no (technical) reason to pre sort the data.
And what is slow?
Show a log of the performance, and tell us what your requirement is.

If we ignore your code a talk about merge, yes, in SQL you don't need to pre sort the data. But SQL usually does that for you behind the scenes, and that isn't necessarily faster (unless you have lots of RAM).
Data never sleeps
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 369 views
  • 0 likes
  • 4 in conversation