topic Re: use proc sql to rewrite code with "data set" in SAS Programming

use proc sql to rewrite code with "data set"

Bal23 — Thu, 11 Aug 2016 14:22:54 GMT

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.

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

Doc_Duke — Thu, 11 Aug 2016 14:34:48 GMT

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?

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

RW9 — Thu, 11 Aug 2016 14:55:33 GMT

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;

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

LinusH — Thu, 11 Aug 2016 14:56:00 GMT

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).

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

Bal23 — Thu, 11 Aug 2016 15:19:02 GMT

Thank you!

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

Bal23 — Thu, 11 Aug 2016 15:21:04 GMT

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